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]
 

psulions83

Board Regular
Joined
Nov 16, 2015
Messages
109
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

Board Regular
Joined
Nov 16, 2015
Messages
109
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

New Member
Joined
Jul 17, 2018
Messages
4
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

Board Regular
Joined
Nov 16, 2015
Messages
109
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

New Member
Joined
Jul 17, 2018
Messages
4
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

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
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.

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;">31</td><td style="text-align: right;;">13.25</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">15</td><td style="text-align: right;;">10</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">20</td><td style="text-align: right;;">10</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">25</td><td style="text-align: right;;">11.25</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">39</td><td style="text-align: right;;">19.25</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">55</td><td style="text-align: right;;">35</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">95</td><td style="text-align: right;;">87.5</td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet138</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E1</th><td style="text-align:left">=IF(<font color="Blue">D1>0,10+MEDIAN(<font color="Red">D1-20,30,0</font>)*0.25+MEDIAN(<font color="Red">D1-30,50,0</font>)*0.5+MEDIAN(<font color="Red">D1-50,100,0</font>)*1,0</font>)</td></tr></tbody></table></td></tr></table><br />
 

Forum statistics

Threads
1,086,198
Messages
5,388,347
Members
402,115
Latest member
ypompoms

Some videos you may like

This Week's Hot Topics

Top