OK, as you have a running total, the formulas are shorter. so G6:
Formula:
=IF(F6="","",SUM(F$6:F6))
and copy down
(note that I added the IF part, to hide repeating total down to the bottom of the table when a cell with mileage is empty)
Then H6 using the same concept as in posts above, but utilizing already calculated running total, and again IF to hide Ł0.0
Formula:
=IF(G6="","",MIN(G6,10000)*0.45+MAX(0,G6-10000)*0.25-SUM(H$5:H5))
and copy down
Then at the total line in F and H columns - sums, while in G37:
Formula:
=MAX(G6:G36)
These 3 cells (F37:G37 can be directly copied into next columns, so M37:O37, T37:V37 etc.
The formulas for row 6 in next month have to be a bit modified, to include also information from previous one, so in N6 and copy down:
Formula:
=IF(M6="","",G$37+SUM(M$6:M6))
And in O6 and copy down:
Formula:
=IF(N6="","",MIN(N6,10000)*0.45+MAX(0,N6-10000)*0.25-SUM(O$5:O5)-SUMIF($H$5:H$5,"Cost",$H$37:H$37))
The later one could be shorter -
Formula:
=IF(N6="","",MIN(N6,10000)*0.45+MAX(0,N6-10000)*0.25-SUM(O$5:O5)-H$37)
but having this extended version, we can copy this 2 columns to next month (N6:O36 copied to U6:V36), and next and so on.
Enjoy!
Bookmarks