I know there are some gurus here.
I have a table that looks like this
Machine type ; Benchmark figure ; Memory ; Unit price Sheet 2 Columns E,F,G,H
Now what I have is a list of requirements.
Application; Benchmark requirement; Memory requirement, Sheet 1: Columns A,B,C
What I am trying to do is lookup the the number of Machine types I need at the best price for the application. (also needs to fulfil the memory requirement)
i.e. Min(Roundup(Benchmark requirement/(Benchmark figure),0)*Price
I'll then do the same in respect to memory to find the.
It seems you can't Divide by a range to find the minimum value which is a pain in the butt.
Comments
If I have a column that divides a fixed value by the benchmark value and do a roundup(min(Column),) on that column I get the lowest number, e.g 1 however if on a row by row basis I do roundup(Min(C2*Table!B2:B)) I get the maximum value
In Sheet 2, column I, put the unit/benchmark price, i.e., cell I2 is H2/F2 etc.
Then the minimum price you need is, I believe,
MIN($B2*Sheet2!$I$2:$I$8+IF(MOD($B2,Sheet2!$F$2:$F$8)=0,0,Sheet2!$H$2:$H$8-MOD($B2,Sheet2!$F$2:$F$8)*Sheet2!$I$2:$I$8))
(This is if your data is in rows 2-8, which is what I did in my little test sheet).
Machine1 1000
Machine2 2000
Machine3 5000
Machine4 3333
If my requirement is 10000
Then I would need 10 Machine1s, 5 Machine2s or 2 Machine3 or 4 Machine4s (not 3.33333)
The problem arises with the $B2*Sheet2$I2:$I8 statement in the MIN statement (could also be the ColumnF. it does not return the minimum value
(Roughly speaking, when it looks at Machine4 in your example, it first estimates you need 3.333, but then that MOD condition tells it that this is wrong, so it adds in the cost of one extra unit, but then subtracts the .333 fraction, so you end up with the cost of exactly 4 units. I won't say that it's elegant, but it gave the right value in the range of numbers I tested it on).
MIN(ROUNDUP($B2/Sheet2!$F$2:$F$8,0)*Sheet2!$H$2:$H$8)
Does that not work for you?