Forum Discussion

Slyfox6915's avatar
Slyfox6915
Copper Contributor
Jul 25, 2022
Solved

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 $ NP...
Formulas and Functions
  • mtarler's avatar
    mtarler
    Jul 25, 2022
    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?
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jul 25, 2022

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))
  )
  • mtarler's avatar
    mtarler
    Silver Contributor
    Jul 25, 2022
    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.
    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor
      Jul 25, 2022

      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.

    • Slyfox6915's avatar
      Slyfox6915
      Copper Contributor
      Jul 25, 2022
      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

Resources