I have a spreadsheet to log my mileage for car allowance.

I have 12 sheets (1 for each month) and they have a row for each day of the month.

Is it possible to link a cell that tally’s up the number of business miles for all of the days in the month, giving a monthly total, then that monthly total has a formula to multiply that amount by .45p per mile.

THEN!! Is there a formula that says once that rolling tally reaches 10,000 it then defaults to .25p per mile??

Yes it is possible

The mileage formula would be something like =(sum(b1:b20)*.45). The complicated bit is doing it for the over 10’000, would probably have to introduce some form of IF statement.

Look up the “IF” command e.g.

To convert total mileage to money:

=<cell with mileage> * IF (<cell with milage> > 10000,0.25,0.45)

Yes just use an IF to give the .45 or .25 if >10000

=IF(A1>10000, “0.25”, “0.45”) would that not work ?

Very easy to do.

You need an ‘if’ formula in the cell, something a bit like this:

=if(sum(click on each cell on each sheet and separate with commas)>10000,sum(same cells again)*25,sum(same cells again)*40)

Although the true and false options may be the wrong way round.

Matt

Example..
In the first sheet that we will call JAN.
Column A. Give the column a heading of Day in cell A1. Then cell A2 downwards put 1 to 31 for the days of the month.
Column B give a heading of milage and enter your milage in each cell for the day of the week.
At the end of column B enter =SUM(B2:B32)
Select all the cells and copy to a new sheet 12 times and rename each to the month..

I think its a bit more complicated than that. Wont the above formulas multiply the whole lot by 25p if there is more than 10000 miles. I presume that you want to still keep the first 10000 at 45p and then any additional at 25p?

Try:-
=IF(A1<10001,A1*0.45,((A1-10000)*0.25)+4500)

Edit: Where A1 is the cell that contains the cumulative total

mjb has it

If this is to keep within hmrc mileage limits, I think he needs something that only changes the rate from that point on once he has 10k miles, rather than all his past mileage.

So, if mileage is in column C, and the £ to be claimed in D, make C1 a brought forward cumulative total from the previous month. then in column D you need a formula that:

1. Adds up cumulative mileage from the rows above, subtracts that from 10k, and gives the higher of the result of that and zero, to avoid a negative number.
2. Takes the lower of the mileage for that day and the result of (1)
3. Multiply the result of (2) by 0.45
4. Subtract the result of (2) from the days mileage
5. Multiply the result of (4) by 0.25
6.add (3) plus(5).

So a combo of MIN, MAX, SUM, + and *

Enjoy!

Edit: mjbs is more elegant and does what op has asked, but doesn’t cover the day when he goes over 10k, and he would have to go back from the cumulative figures to get his monthly claim, which I assume is what he needs.

Following on add another Sheet and call it TOTALS
Column A give a heading of Total Milage
Column B give a heading of Milage At 0.25
Column C give a heading of Milage At 0.40

Cell A2 enter =SUM(JAN!B33,FEB!B30,MAR!B33)
you’ll need to enter evervsheey and cell in that formula for all 12 sheets
So you just need to add the JAN!B33, just change JAN to each sheet name.

Cell B2 enter =SUM(A2-10000)
Cell B3 enter =SUM(B2*0.25)
Cell C2 enter =SUM(A2-B2)
cell C3 enter =SUM(C2*0.45)
Cell D3 enter =SUM(B3:C3)

give me an email and I’ll send you the spreadsheet

Thinking about it a bit more, you’re asking for something slightly more than that.

Maybe best to have 4 totals at the bottom of each month (you could hide any you don’t want to see) as follows

Mileage for month – sum of daily miles on sheet (e.g. SUM(B4:B34))
Total mileage to date – mileage for month above + total mileage to date from previous month
Total cost to date – =IF(A1<10001,A1*0.45,((A1-10000)*0.25)+4500) where A1 is total mileage to date above
Cost for month – total cost to date above – total cost to date from previous month

For the first month you’ll just have to put in a value of zero for the mileage and cost to date.

Hope that all makes sense, there are other ways but I think that is the easiest to follow.

Ive put a copy on my webspace here
http://www.zen74279.zen.co.uk/EXCEL/

Theres two versions xlsx for excel 2007/2010
and xls for excel 2003

I think…
Each sheet has 4 columns (starting at A1) and titled:
Date
Mileage
Cumulative Milage
Allowance

Then below each heading enter:
Type in 1st of the month and drag down to the end of the month
enter daily milage
=IF(B2>0, SUM($B$2:B2), “”) and drag down to the end of the month
=IF(B2>0, IF(C2<100001, B2*0.45, B2 *0.25), “”) and drag down to the end of the month

Copy for each month
Sheet 13 is then just a sum of all monthly milages and allowances

Edit You could use a vlookup with a small table (4 cells) at the bottom of each sheet to make managing future changes easier!

If this is standard government mileage then the 10000 miles is an annual value not a monthly one. I also assume that the OP will want to claim his mileage at the end of every month.

For example if he drives 3000/month then January, February and March’s miles will all be charged at 0.45 and he can claim £1350 each month. However in April he will pass the 10,000 mile mark and the so the first 1000 miles will be at 0.45 and the last 2000 will be at 0.25 and he can claim £950. Mileage in future months will all be charged at 0.25 or £750/month.

That means you have to keep a running total through the year and work out at which point it goes over 10,000 miles and start claiming at the lower rate. Simplest way I can think of is I mentioned above.

Can you bang all the mileage data in a single sheet? This would make summarising the data easier and save faffing with carry forward and brought forward figures?

Can you bang all the mileage data in a single sheet? This would make summarising the data easier and save faffing with carry forward and brought forward figures?

It would be assuming that no other information is being recorded like where he went. You’d still need to calculate the monthly and running totals etc. as above to work out the amount to claim each month, it’s just they’d all be on one sheet.

Easiest way to keep a cumulative total is just add a column on the right, and put a formula that adds the miles for that entry to the one above, eg, =D2+C3 Going to the next month, just link the first cell in the cumulative column to the total for the previous month.

Then use an IF to set the rate based on the cumulative column, eg =if(D3>10000,0.45,0.25)

Notepad and pencil was so much easier

Easiest way to keep a cumulative total is just add a column on the right, and put a formula that adds the miles for that entry to the one above, eg, =D2+C3 Going to the next month, just link the first cell in the cumulative column to the total for the previous month.

Then use an IF to set the rate based on the cumulative column, eg =if(D3>10000,0.45,0.25)

But the rate may change during a days mileage. For example if D2 is 9500 miles and C3 is 501 miles, D3 will charge all the miles at the lower rate when in reality it should be 500@0.45 and 1@0.25. That’s why its better to total the mileage over the month before calculating he cost.

IF is a bit of a sledgehammer to crack the nut of splitting the mileage up to 10000 from that over 10000.

Assuming cell A1 contains your total mileage then:

=MIN(10000,A1) gives your mileage up to 10000 miles
=A1-MIN(10000,A1) gives your mileage above 10000 miles

If you wanted to stick it all in one cell that calculated the total value of your mileage then
=(MIN(10000,A1)*0.45)+((A1-MIN(10000,A1))*0.25) should do the trick.

If you wanted to make it more flexible then you could pull out the 10000, 0.45 and 0.25 to cell references – makes it easier to tweak the sheet if the rates change in the future.

Applied to the spreadsheets you’ve uploaded then cell C2 on the TOTALS sheet should contain the formula =MIN(A2,10000) and B2 should contain =A2-C2

I’m voting for mjbs solution as the winner of this nerd-off. 🙂

Goldigger wins most helpful for actually making the spreadsheets., but doesn’t do monthly totals!

Zero marks for me as couldn’t be bothered to type a formula on the tablet!

thanks for the very helpful replies, as a very basic excel user I’ll now set aside my weekend in attempting to follow the instructions and set aside numerous objects to smash up in angst 😆