# Need assistance in working out cost that falls between a range

#### houtaff

##### New Member
Afternoon all
Need some help please. I have a rate chart that if a cubic foot falls within a range then i can bill a certain amount.

Example
0-20 = \$2.99
21-40 = \$2.67
41-60 = \$2.41

This is based on cubic footage of a box which is calculated and placed in a cell so if the cubic footage is 6.12 CFT then i need something to be able to look at a table and read that as the CFT falls between 0-20 then it will be \$2.99 multiplied by the CFT (which is 6.12) totaling \$18.29

 L W H CFT RATE COST 21 21 24 6.12 2.99 18.29

<tbody>
</tbody>

I have 8 ranges that i calculate and manually punch this info into our system and i would love something that recognises that the CFT would fall between a certain range and then calculate the correct rate and then final cost

Please be gentle with me if im posting where i shouldn't as this is my first time posting

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

#### bbott

##### Well-known Member
Welcome to the board.

Assuming your rate is in column D, try this in E2:

=LOOKUP(D2,{0,21,41},{2.99,2.67,2.41})

#### houtaff

##### New Member
Welcome to the board.

Assuming your rate is in column D, try this in E2:

=LOOKUP(D2,{0,21,41},{2.99,2.67,2.41})

ok E2 would be the rate of \$2.99 where as the calculation would be based off D2 which is 6.12 CFT

Does that make sense?

#### bbott

##### Well-known Member
The formula generates the appropriate rate based on the value in D2. Is that not what you wanted?

#### houtaff

##### New Member

its is exactly what i was looking for when i punched in the formula.

Because i had a manual spreadsheet that one of my team had filled in already i was able to drop the formula in and see that 99.9 % were dead on however, it did show me that we nearly lost out on \$2000 which is a big error

I am very grateful for your assistance as this will automate the process for my guys who work offsite and have to manually calculate things

Very very much appreciated

#### houtaff

##### New Member
ok last one on this one thread for me.

If the total is less than \$45 then i have a minimum charge. Can this be calculated into a formula as right now i have a very simple one that reads the multiplication between 2 cells = dollar amount.

i need something to add to this formula that says if the result is under \$45 then enter \$45

#### joes2580

##### New Member

Try This:

=IF(D1<=45,"45",D1)

#### houtaff

##### New Member
Try This:

=IF(D1<=45,"45",D1)

do i just add that to the end of my simple =sum(a2*a3) formula? im trying to have one formula that says the sum of 2 cells if less than \$45 then it needs to be \$45 but if its greater than \$45 then use the amount given

Hope that makes sense

#### bbott

##### Well-known Member
do i just add that to the end of my simple =sum(a2*a3) formula? im trying to have one formula that says the sum of 2 cells if less than \$45 then it needs to be \$45 but if its greater than \$45 then use the amount given

Hope that makes sense

Which cells are you summing? In the equation that you have, you're actually multiplying. I'll assume that you want to sum A1:A2 and if that sum is less than 45, charge 45. Like so:

=MAX(A1+A2,45) Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,164,489
Messages
5,837,630
Members
430,506
Latest member
TonyIbbs ### 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