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

[blackrose91]

[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]

 

[psulions83]

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

 

[psulions83]

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.

 

[blackrose91]

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)?

 

[psulions83]

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.

 

[blackrose91]

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!

 

[jtakw]

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
	D	E
1	31	13.25
2	15	10
3	20	10
4	25	11.25
5	39	19.25
6	55	35
7	95	87.5
Sheet138

Cell Formulas
Range	Formula
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)