# Equals how many?

#### Freedive

##### New Member
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

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### barry houdini

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP
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

##### New Member
Many thanks Barry

Replies
10
Views
779
Replies
0
Views
102
Replies
6
Views
101
Replies
1
Views
557
Replies
6
Views
193

1,191,517
Messages
5,987,054
Members
440,074
Latest member
Emmanuelian

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back