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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Rob HensonFinance 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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
Rob HensonFinance 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.