Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    May 2005
    Location
    west drayton, Middlesex, United Kingdom
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #4
    Star Lounger
    Join Date
    May 2005
    Location
    west drayton, Middlesex, United Kingdom
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #5
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Birmingham, England
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    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
    Jerry

  7. #7
    Star Lounger
    Join Date
    May 2005
    Location
    west drayton, Middlesex, United Kingdom
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •