Forum Discussion
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
- 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?
12 Replies
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.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.
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?
