Spreadsheet help

Axe_meister

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.

mart

If the problem is just the division then I'd add an extra column to sheet 2 of "reciprocal benchmark figure", with values 1/F1 etc. Then multiply by that instead of dividing by the benchmark figure.

Axe_meister

Odd still doesn't work.
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 

Roland

Vlookup?

Axe_meister

Vlookup won't work in this instance as there is nothing to lookup

mart

Ok, I've found one way to make it work:
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).

Axe_meister

The problem is you need an integer number before you calculate the price. I..e. if I have a number of machine types with Benchmark values of say
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

mart

Indeed, that's why I've got that IF(MOD ... stuff in there - it serves to essentially fudge things to get back the cost of buying an integer number of units. 

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

mart

Actually, scrub all that, on the version of Excel I've got, the obvious formula works:

MIN(ROUNDUP($B2/Sheet2!$F$2:$F$8,0)*Sheet2!$H$2:$H$8)

Does that not work for you?

Axe_meister

I had to use any arrayformula