How to calculate costs, when you have different rates for different criteria?

blackrose91

New Member
Joined
Jul 17, 2018
Messages
4
[FONT=&quot]I'm looking for some help as whilst I have a basic working knowledge of excel, in my job I'm fast having to get to grips with more complex formulas.

[/FONT]

[FONT=&quot]I need to work out the total cost for something, where you have a flat rate, and then an additional cost that changes depending on how much of something you have.

[/FONT]

[FONT=&quot]So an example:

[/FONT]

[FONT=&quot]You get expenses paid for millage. If you drive 0-20 miles, you'll get £10. Between 21-30 miles you get an extra 25p, per mile, on top of the £10 you get for your first 20 miles. Between 31-50 miles you get an extra 50p per mile. Between 51-100 miles you get £1 per mile and so on.

[/FONT]

[FONT=&quot]It's not the best example, but hoping it gives an idea of what I'm after.

[/FONT]

[FONT=&quot]If I was doing this by hand I'd know how to work it out, but I'm not to sure what kind of formula I need to be using - I've never had to work with complex formulas past "=sum" until now.

[/FONT]

[FONT=&quot]If anyone has any examples they can share or can point me in the right direction of what kind of things to google I'd be most grateful !

[/FONT]

[FONT=&quot]Thanks[/FONT]
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
You can try something like below. You can keep adding new variable as well. I used D1 to put in an amount for the mileage.

=IF(D1<20,10,IF(AND(D1>20,D1<30),20,IF(AND(D1>30,D1<50),30,0)))
 
Upvote 0
The IF statement starts your argument off....IF(FIRST TIER,IF TRUE PUT IN,IF FALSE PUT IN OR MOVE ONTO NEXT IF STATEMENT). Hope it makes sense.
 
Upvote 0
You can try something like below. You can keep adding new variable as well. I used D1 to put in an amount for the mileage.

=IF(D1<20,10,IF(AND(D1>20,D1<30),20,IF(AND(D1>30,D1<50),30,0)))

Hi psulions83

thank you for this. I kinda understand the logic of how if statements work, but what I can't quite get my head around is, say your millage is 21 miles, would this formula return the value to be £10.25 (So £10 for the first 20 miles, and then as it was 25p per mile for anything between 21-30 miles, that gets added on top)?
 
Upvote 0
you can use math within your TRUE statement to address this

=IF(D1<20,10,IF(AND(D1>20,D1<30),(10+0.25),IF(AND(D1>30,D1<50),30,0)))

Lets say you got .25 for each mile up to 20 then .30 for each mile after you could do the multiplication in there.

=IF(D1<20,(D1*0.25),IF(AND(D1>20,D1<30),(D1*0.25)+((D1-20)*0.3),0))

Each different variable you put in there you can keep adding to the math formula.
 
Upvote 0
you can use math within your TRUE statement to address this

=IF(D1<20,10,IF(AND(D1>20,D1<30),(10+0.25),IF(AND(D1>30,D1<50),30,0)))

Lets say you got .25 for each mile up to 20 then .30 for each mile after you could do the multiplication in there.

=IF(D1<20,(D1*0.25),IF(AND(D1>20,D1<30),(D1*0.25)+((D1-20)*0.3),0))

Each different variable you put in there you can keep adding to the math formula.



Thank you for this, I did this in work and it worked :D thanks so much for your time and help, I appreciate it so much!
 
Upvote 0
Hi,

I don't think the formulas provided above is doing the math correctly...

So if someone logged 31 miles for example, he gets 10 (flat) for the first 20 miles, then for the next 10 miles, he gets an additional 0.25 for every mile up to 30, then an additional 0.50 for every mile up to 50 (including the additional 0.25), is that correct?

So it should be 10 + 10*0.25 + 1*0.25 + 1*0.50 = 13.25 ??

If this logic is correct, then use this formula, otherwise, please explain using some samples.


Book1
DE
13113.25
21510
32010
42511.25
53919.25
65535
79587.5
Sheet138
Cell Formulas
RangeFormula
E1=IF(D1>0,10+MEDIAN(D1-20,30,0)*0.25+MEDIAN(D1-30,50,0)*0.5+MEDIAN(D1-50,100,0)*1,0)
 
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

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
Back
Top