+ Reply to Thread
Results 1 to 15 of 15

Formula for calculating mileage claim with multiple cells

  1. #1
    Forum Contributor
    Join Date
    10-13-2008
    Location
    wales (uk)
    Posts
    113

    Formula for calculating mileage claim with multiple cells

    Hello everyone,

    I am after some help.

    I have a spread sheet which has a column which is linked to the adjacent column. It is for working out company mileage claims, basically im inputting the mileage on the left cell for Monday, the the cell to the right will do a calculation or multiplying the mileage. And the next day will be the same etc etc.

    Here is the tricky part, I want the formula to keep track of the total mileage, as up to 10000 miles the rate is 1 mile x .45p, anything after that is x 25p.

    Ive tried using endless amounts of brackets, colorful language etc to no avail.

    Any help would be most appreciated

    Many thanks

  2. #2
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Formula for calculating mileage claim with multiple cells

    Attach a sample Excel workbook (not a picture!). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

    Update your profile about actual Excel version you have.
    Last edited by sandy666; 01-04-2018 at 05:51 AM.

  3. #3
    Forum Contributor
    Join Date
    10-13-2008
    Location
    wales (uk)
    Posts
    113

    Re: Formula for calculating mileage claim with multiple cells

    im on it, give me 5 mins

  4. #4
    Forum Contributor
    Join Date
    10-13-2008
    Location
    wales (uk)
    Posts
    113

    Re: Formula for calculating mileage claim with multiple cells

    here is the spreadsheet
    Attached Files Attached Files

  5. #5
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Formula for calculating mileage claim with multiple cells

    Mileage is less than 10000
    here is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and drag down

    CHOOSE function
    MATCH function
    Attached Files Attached Files
    Last edited by sandy666; 01-04-2018 at 12:01 PM.

  6. #6
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Formula for calculating mileage claim with multiple cells

    C2median(b4,10000-sum(b$3:b3),0)*0.45+median(sum(b$4:b4)-10000,b4,0)*0.25
    try this and copy towards down
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,339

    Re: Formula for calculating mileage claim with multiple cells

    Siva - is that formula quite right at the beginning?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  8. #8
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Formula for calculating mileage claim with multiple cells

    SORRY AliGW i did not observe the "=" sign was missed
    it is

    C4=MEDIAN(B4,10000-SUM(B$3:B3),0)*0.45+MEDIAN(SUM(B$4:B4)-10000,B4,0)*0.25
    Last edited by samba_ravi; 01-04-2018 at 12:25 PM. Reason: cell reference given as C2 instead of C4

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,339

    Re: Formula for calculating mileage claim with multiple cells

    Thanks, Siva!

  10. #10
    Forum Contributor
    Join Date
    10-13-2008
    Location
    wales (uk)
    Posts
    113

    Re: Formula for calculating mileage claim with multiple cells

    thanks a lot peeps, will have a go tonight when im home :-)

    will report back tomorrow. thanks again

  11. #11
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Formula for calculating mileage claim with multiple cells

    Quote Originally Posted by nflsales View Post
    C2=MEDIAN(B4,10000-SUM(B$3:B3),0)*0.45+MEDIAN(SUM(B$4:B4)-10000,B4,0)*0.25
    IMHO C4 rather

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Formula for calculating mileage claim with multiple cells

    Siva, nice use of median... Gives the right answer, though. Mine is a bit more long-winded..

    =SUMPRODUCT(--(SUM($B$4:B4)>{0,10000}),--(SUM($B$4:B4)-{0,10000}),{0.45,-0.2})-SUM($E$3:E3)
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  13. #13
    Forum Contributor
    Join Date
    10-13-2008
    Location
    wales (uk)
    Posts
    113

    Re: Formula for calculating mileage claim with multiple cells

    Well you chaps and lass's are clever arnt you!! thank you so much for a very quick solution, much appreciated. Happy Friday :-)

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,339

    Re: Formula for calculating mileage claim with multiple cells

    We do our best!

    Love your user name, by the way - makes me chuckle every time I see it.

  15. #15
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Formula for calculating mileage claim with multiple cells

    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Calculating driver pay from the mileage they have travelled
    By UKPatriot1980 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-21-2017, 05:51 AM
  2. [SOLVED] Help with creating excel spreadsheet for calculating mileage with multiple rates
    By Annahstar in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-29-2016, 01:18 PM
  3. [SOLVED] Calculating daily mileage payment
    By alarm_guy1 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-13-2013, 02:23 PM
  4. Calculating mileage
    By jamington2004 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-21-2012, 06:53 AM
  5. Replies: 1
    Last Post: 02-08-2012, 11:37 AM
  6. Mileage Claim Formula
    By johndavies in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 08-14-2006, 04:35 AM
  7. [SOLVED] Calculating for personal car mileage?
    By Elau in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 01-16-2006, 06:30 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1