Equals how many?

[Freedive]

Pls assist ...
I have following ... Cell B8 ... a monetary value (say $50)
C8 ... a monetary value (say $120)
in otherwords B8 & C8 are the monetary "spend" parameters.
column D8 is the price per unit for the "spend" parameters B8 & C8
so ... if they want 100 units (enter 100 in cell G5) ... in column H5, i need to know how much it will cost for 100 units.

Bear in mind column B, C & D are from 8 to 30

 

[barry houdini]

Hello Freedive, welcome to MrExcel.

Do the values in D8:D30 reduce as the spend goes up? Isn't it possible with this setup that it's impossible for the "spend" to fit into any category?

E.g. if D8 = $1.21 then 100 units will cost $121 so that cost is greater than the first interval, but if D9 = $1.17 then 100 units will cost $117 which is presumably lower than the second interval.....or have I misunderstood the problem?

 

[Freedive]

say ....
B8 = $50 ... C8 =$120, so spend parameter is between $50 to $120. now if they want to spend say $80. D8 may be $1.21 per unit within these spend parameters. So if I enter $80 in G5, then H5 must show me how many units they get for the $80 spend.
B9 will start at $121 & C9 = $250. D9 would be less yes at $1.15 and so on through to cells 30 or so, but only cells G5 & H5 to display answers.

 

[barry houdini]

OK, I though G5 would show the quantity but if it shows the amount to spend try this formula in H5 to give the number of whole units that can be bought

=INT(G5/LOOKUP(G5,B$8:D$30))

 

[Freedive]

Many thanks Barry