Need assistance in working out cost that falls between a range

houtaff

New Member
Joined
May 29, 2015
Messages
10
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

LWHCFTRATECOST
2121246.122.9918.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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

bbott

Well-known Member
Joined
Feb 5, 2010
Messages
2,350
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
Joined
May 29, 2015
Messages
10
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
Joined
Feb 5, 2010
Messages
2,350
The formula generates the appropriate rate based on the value in D2. Is that not what you wanted?
 

houtaff

New Member
Joined
May 29, 2015
Messages
10

ADVERTISEMENT

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
Joined
May 29, 2015
Messages
10
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
Joined
Feb 18, 2015
Messages
36

ADVERTISEMENT

Try This:

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

houtaff

New Member
Joined
May 29, 2015
Messages
10
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
Joined
Feb 5, 2010
Messages
2,350
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,591
Messages
5,637,288
Members
416,963
Latest member
samfuge

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top