# 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

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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})

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?

The formula generates the appropriate rate based on the value in D2. Is that not what you wanted?

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

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

Try This:

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

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

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)

Replies
2
Views
363
Replies
5
Views
653
Replies
5
Views
385
Replies
6
Views
786
Replies
4
Views
1K

1,218,944
Messages
6,145,357
Members
450,611
Latest member
JodiWe

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