SOLVED

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

Occasional Contributor

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

 Prix de vente1 Batteries2 Prix2 Prix de vente2 Batteries3 Prix3 Prix de vente3 Batteries4 Prix4 Prix de vente4 36,99 \$ N6-4.5T1 17,56 \$ 23,99 \$ 46,99 \$ NPXL-35FR 32,88 \$ 42,99 \$ 44,99 \$ 72,99 \$ NP12-12FR 58,76 \$ 76,99 \$ N12-12T2 31,13  \$ 43,00 \$ 100,99 \$ NPXL-35FR 70,86 \$ 92,99 \$ N12-7.6T2 44,76  \$ 61,00 \$ 152,99 \$ NP12-12FR 122,62 \$ 159,99 \$ 211,99 \$ NP18-12BFR 166,56 \$ 216,99 \$ N12-18NB 93,06  \$ 126,00 \$ N12-20NB 102,86 \$ 138,99 \$ 201,99 \$ NPXL-35FR 141,92 \$ 184,99 \$ N12-7.6T2 89,52  \$ 121,00 \$ 100,99 \$ NPXL-35FR 70,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

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

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

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

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

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

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

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?

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

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))
)

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

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.

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

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

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

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

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

here is an example

@mtarler

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

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 (Occasional Contributor)
Solution

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

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?

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

i just need highest of each brand not second highest

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

@Slyfox6915 Here are 2 options you can use:

see them in the attached