Ranking calculations [solved]

[fatbeard1]

I want to determine a order quantity based on a required quantity and available price breaks, to minimise total costs.
I prepared a small section of the spreadsheet to clarify: orders.ods (15.2 KB).
In this spreadsheet, the price breaks are in cells F3:H4, the required quantity is in cell A4.

I found a way to determine the cost (formula in cell J4) which works excellent (experiment with the required quantity to see), no problems there.
What I really need is the quantity to order (cell I4), which could be calculated by using the OFFSET(), RANK() and IF() funcions; but the RANK() function only works on an array, not on a series of calculated values…

Is there a way (short of adding a bunch of cells to get the array needed for the RANK() function) to achieve this?
So far, I came up empty…

[additional info]
I came up with =IF(IF(D4>H3,D4*H4,H3*H4)=J4,MAX(D4,H3),"other") for the largest step, but aparently this trick is not nestable: when I substitute “other” with IF(D4>G3,D4*G4,G3*G4)=J4,MAX(D4,G3),D4 it produces a 504 error…

[karolus]

Hallo
I4:

=MINIFS(F3:H3;F3:H3;">="&$D4)

J4:

=MINIFS(F$3:H$3*F4:H4;F3:H3;">="&D4)

enter the last one as arrayformula with: ctrl shift enter

[fatbeard1]

Alas, this does not work: for a required quantity of 120 I get a order quantity of 0, for a required quantity of 20 I get a order quantity of 100.
Neither is correct: for 120 required I need to order 120 pieces (dûh), for 20 required I need to order 20 to get the best price.

What may have influenced is my current version:

Version: 7.3.7.2 / LibreOffice Community
Build ID: 30(Build:2)
CPU threads: 6; OS: Linux 5.15; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Ubuntu package version: 1:7.3.7-0ubuntu0.22.04.11
Calc: threaded

… But you got me thinking again: turns out my nesting of the IFs was incomplete… Must be the weather.
Final solution for I4: =IF(IF(D4>H3,D4*H4,H3*H4)=J4,MAX(D4,H3),IF(IF(D4>G3,D4*G4,G3*G4)=J4,MAX(D4,G3),D4))