how to calculate cumulative kilometres done with various cut-off points.

This is for a mileage calculation. Essentially, someone claims mileage i.e. Km done. However the rate they are entitled to is dependent on the CUMULATIVE miles they have done. e.g. in Week 2 someone has done 2250 km but because they have already done 300 in the previous week, they get paid 1200 in Band 1 (which has a cut-off of 1500) and 1050 in Band 2. And so on for the other weeks...What's required here are just the km. I can do the actual calculation with a sumproduct formula in another cell.
EE_mileage_calculation.xlsx
LVL 1
agwalshAsked:
Who is Participating?
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
See corrections in attached

However, I don't agree with your user's reckoning for Week 5 onward.

The 6000 miles in Week 4 took the cumulative total to 7250. The first 250 took the user to the 1500 limit for Band 1 and then the next 4000 went to the 5500 limit for Band 2, hence 1750 went into Band 3. Therefore all of the 3300 for Week 5 must also go into Band 3.

Week 6 mileage of 2500 is still below threshold for Band 3 so can all go to band 3.

Week 7 mileage of 24000 (good going driving that in a week, >3400 per day) takes the cumulative to over 25000 with first 11950 taking up to 25000 limit and balance of 12050 going in Band 4.
EE_mileage_calculation-v4.xlsx
0
 
Rob HensonFinance AnalystCommented:
Try these for size, starting in row 5 copied down:

column D:   =IF($C5<D$3,B5,MAX(0,D$3-SUM(D4:D$4)))
column E:   =IF($C5<E$3,MAX(0,$C5-SUM(D$4:D5)-SUM(E$4:E4)),0)
column F:   =IF($C5<F$3,MAX(0,$C5-SUM(D$4:D5)-SUM(E$4:E5)-SUM(F$4:F4)),0)

I have had to convert the values in row 4 to text so that they didn't get included in the values being counted but needed to include row 4 to avoid circular reference.

Values in row 4:
E4:  =TEXT(E3-D3,"0")
F4:  =TEXT(F3-E3,"0")

Hope that works. See attached.
EE_mileage_calculation.xlsx
0
 
agwalshAuthor Commented:
Definitely getting there! I checked with my user and she reviewed the numbers. There are a couple of discrepancies...please see attached file! Thank you so much for this :-). They are marked in yellow..
EE_mileage_calculation-v2.xlsx
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Rob HensonConnect With a Mentor Finance AnalystCommented:
Apologies for the delay in coming back to you, been laid up with cold.

New formulas for columns E & F:

col E: =IF($C5<E$3,MAX(0,$C5-SUM(D$4:D5)-SUM(E$4:E4)),E$3-SUM(D$4:E4))
col F: =IF($C5<F$3,MAX(0,$C5-SUM(D$4:E5)-SUM(F$4:F4)),F$3-SUM(D$4:F4))

The change is in the last part of the IF statement, previously this was just putting zero but it is now deducting what has already been accounted for.

Also slight change in the middle of col F formula; rather than two SUM formula for D & E separately, it is now one SUM for both columns.

Also, column G, balance of mileage from column B needs to include columns D to F and not just F:

=B5-SUM(D5:F5)

See attached.
EE_mileage_calculation-v2.xlsx
0
 
agwalshAuthor Commented:
This is brilliant...just one last bit. Got the user to check it and on the sheet EE Question MC you will see the discrepancy for Week 5 and Week 7. Thank you so much.
EE_mileage_calculation-v4.xlsx
0
 
agwalshAuthor Commented:
Had a very very tricky mileage band calculation to do. Rob did a truly outstanding job on this. Has saved the user hours of work. Thank you
0
 
Rob HensonFinance AnalystCommented:
Thank you for the great feedback.

Glad I was able to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.