1. ## Mileage Report (2003)

Good afternoon

I have been asked by a friend to design a mileage claim report for them as they use their own vehicle for business purposes and can claim a mileage allowance which in the UK is

2. ## Re: Mileage Report (2003)

With a mileage in A1, the following will calculate the rate:

=MIN(A1,10000)*0.4+MAX(A1-10000,0)*0.25

3. ## Re: Mileage Report (2003)

I would just make 2 sheets 1 for all the values and then use a pivot table to summarize on the other sheet.

I do not know how you are planning on setup, but the rate may not be possible for a particular 'transaction/ row if it changes during those miles. Do you want the rate for the miles in that transaction or something else? If you want the miles for that row, it will be 0.4, 0.25 or it could be a weighted average if some of them are for 0.4 and some are at 0.25.

You can make the formulas using SUMIF and the individual (or if only for 1 person) just a sum. The date can be included as a column.

Steve

4. ## Re: Mileage Report (2003)

Thanks Steve

I have never used a pivot table before I will go home and curl up with the Excel Bible and see what it has to say about it.

Cheers

Danny

5. ## Re: Mileage Report (2003)

I think I'd do it on one sheet!
Create a spare column with the formula = month([cell with the date in it]) and hide it.
Do the sumifs on the mileage each month.
Sample attached sort of illustrates (a lump of one of mine concerning paylaods of empy bottles for recycling!)
Best of luck.

6. ## Re: Mileage Report (2003)

Hi Danny

I had a look at this last night, slept on it and put this together for you this lunchtime.... I think this works but you may want to check it out.

I have created a summary front sheet for mileage for a user. values in Column B can be linked to the individual monthly totals worksheets. Column C is the mileage accrual. I have then created two more columns for Under 10000 and Over 10000.

I think you need to look at the formulas I have created in C and D columns, just be aware that I have a habit of using named cells so L1 and L2 refer to Under and Over that you will see in the formulas.

Have a look and see what you think change the figures in column B and see how the summary changes

7. ## Re: Mileage Report (2003)

Thanks to all for your suggestions nad my apologies for my tawdry response, I was however smitten by the dreaded flu bug (not avian fortunately!!).

I have not yet had time to fully digest everybodies suggestions and I am sue I will be back with some questions later as I would like to have the 12 seperate worksheets with the 13th being something like Jezza's

Thanks again

Danny

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•