SOLVED

has to be a way for finding first and second highest price in 4 seperate columns

Copper Contributor
Prix de vente1Batteries2Prix2Prix de vente2Batteries3 Prix3 Prix de vente3Batteries4 Prix4 Prix de vente4
36,99 $N6-4.5T117,56 $23,99 $       
46,99 $NPXL-35FR32,88 $42,99 $       
44,99 $          
72,99 $NP12-12FR58,76 $76,99 $N12-12T2   31,13  $43,00 $   
100,99 $NPXL-35FR70,86 $92,99 $N12-7.6T2   44,76  $61,00 $   
152,99 $NP12-12FR122,62 $159,99 $       
211,99 $NP18-12BFR166,56 $216,99 $N12-18NB   93,06  $126,00 $N12-20NB102,86 $138,99 $
201,99 $NPXL-35FR141,92 $184,99 $N12-7.6T2   89,52  $121,00 $   
100,99 $NPXL-35FR70,96 $92,99 $N12-7.6T2   44,76  $61,00 $   

 

currently i can do =MIN(M8;P8;S8;V8) but i cannot choose first or second lowest 

12 Replies

try SMALL() or LARGE() and group the ranges inside of ()

eg =SMALL( (M8:M20; P8:P20; S8:S20; V8:V20) ; 2)

wont work as i need it to be in line as they are all different articles in column
i need to know within salesprice1 salesprice2, salesprice3 and 4 which is highest and second highest and same for lowest

sorry I'm lost. what does "need it to be in line" mean? you want the highest and second highest for what then. you tried: MIN(M8;P8;S8;V8) then why not =SMALL( (M8; P8; S8; V8) ; 2) for second smallest?

@Slyfox6915 

This may look somewhat alien; it is a 365 solution.

MinPriceλ
= LAMBDA(k,
    LAMBDA(record,
        SMALL(CHOOSECOLS(record, 1, 4, 7, 10), k)
    )
)

WorksheetFormula
= HSTACK(
    BYROW(SalesTable,MinPriceλ(1)),
    BYROW(SalesTable,MinPriceλ(2))
  )
and don't forget it also requires Beta function
but I don't know if that does what they want as I still don't know what they want.
was too simple :( thank you

now how do i tell excel to look for biggest of the big prices and biggest of the small prices :( some kits have 4 prices and some only have 2

i guess i will need some if's
let me try and help you understand and perhaps help me understand :)

i have battery packs which can be constructed with different kind and brands of batteries

Yuasa and Nitro brands

i need highest sales price of YUASA and highest sales price of Nitro which is always lower than the Yuasa
Parts Desc Connecteur Fuse Fils Plastique Casing Programmation Nbr de batt Markup1 Batteries1 Brand price1 sales price1 Batteries2 Brand price2 sales price2 Batteries3 Brand price3 sales price3 Batteries4 Brand price4 sales price4
RBC1-GDF Batteries GDF Compatibles pour RBC1 0 0 0,1 0 0 0 1 0,3 NP4.5-6 YUASA 13,94 18,99 N6-4.5T1 NITRO 8,83 11,99 N/A #N/A #N/A
RBC2-GDF Batteries GDF Compatibles pour RBC2 0 0 0 0 0 0 2 0,3 NPX-35 YUASA 72,18 93,99 NPXL-35FR YUASA 65,76 85,99 #N/A #N/A
RBC3-GDF Batteries GDF Compatibles pour RBC3 0 0 0,1 0 0 0 1 0,3 N6-12T1 NITRO 16,53 22,99 #N/A #N/A #N/A
RBC4-GDF Batteries GDF Compatibles pour RBC4 0 0 0 0 0 0 2 0,3 NP12-12 YUASA 112,18 145,99 NP12-12FR YUASA 117,52 152,99 N12-12T2 NITRO 62,26 85 #N/A
RBC5-GDF Batteries GDF Compatibles pour RBC5 0 5 0,1 0 0 0 2 0,3 NPX-35 YUASA 77,28 100,99 NPXL-35FR YUASA 70,86 92,99 N12-7.6T2 NITRO 44,76 61 #N/A
RBC6-GDF Batteries GDF Compatibles pour RBC6 0 5 0,1 0 0 0 2 0,3 NP12-12 YUASA 117,28 152,99 NP12-12FR YUASA 122,62 159,99 #N/A #N/A
RBC7-GDF Batteries GDF Compatibles pour RBC7 5 5 0 0 0 0 4 0,3 NP18-12B YUASA 315,44 410,99 NP18-12BFR YUASA 323,12 420,99 N12-18NB NITRO 186,12 252 N12-20NB NITRO 205,72 277,99

here is an example

@mtarler

@mtarler 

You are correct.  I could have, and should have, written a version with INDEX/SEQENCE.

I was more focussed on the fact that the formula would not allow me to provide k={1,2} in the SMALL function.   I reckon that about 80% of my array solutions require 'nested arrays' or 'arrays of ranges' and about the same proportion of my time is spent looking for workarounds! 

 

REDUCE and HSTACK works, MAKEARRAY and Thunks work but are overly CPU intensive.  MID expands an array of fixed-length strings to an array of arrays, but I resent having to scratch around for solutions that Excel should provide by default.  All that to maintain compatibility with obsolete versions of Excel that I was happy to place on the bonfire.

best response confirmed by Slyfox6915 (Copper Contributor)
Solution
thank you for the sample sheet. that makes things much easier.
So you want "highest sales price of YUASA and highest sales price of Nitro" but is that per row or for all rows and what about 2nd highest and 2nd lowest?
i just need highest of each brand not second highest

@Slyfox6915 Here are 2 options you can use:

mtarler_0-1658843272159.png

see them in the attached

1 best response

Accepted Solutions
best response confirmed by Slyfox6915 (Copper Contributor)
Solution
thank you for the sample sheet. that makes things much easier.
So you want "highest sales price of YUASA and highest sales price of Nitro" but is that per row or for all rows and what about 2nd highest and 2nd lowest?

View solution in original post