+ Reply to Thread
Results 1 to 3 of 3

Using a formula to calcualte different mileage rates

  1. #1
    Registered User
    Join Date
    01-17-2012
    Location
    Manchester, England
    MS-Off Ver
    Excel 2007
    Posts
    1

    Using a formula to calcualte different mileage rates

    Hi,

    I have attached a file in which i need some help using a formula to calculate a milage rate once it hits a certain level. So in my sheet the limit for mileage is 10,000 at which these can be claimed at 0.45p and anything thereafter at 0.25p. The opening miles is 9800 which means there are 200 miles left at 0.45p and this is reached on the 4th when 30 miles are done in the day - so i need to calculate 20 @0.45p and10 @0.25p. But i need a formula which will say anything which is under 10,000 charge at 0.45p and anything after that threshold at 0.25p.

    I hope this makes sense -this is my first post!

    Regards

    Andrew.Mileage Rate.xlsx

  2. #2
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: Using a formula to calcualte different mileage rates

    Use

    =MIN($C3,($D$18-$D$17-SUM($C$2:$C2)))*0.45+MAX(0,(SUM($C$2:$C3)+$D$17-$D$18))*0.25

  3. #3
    Registered User
    Join Date
    01-10-2012
    Location
    colorado, usa
    MS-Off Ver
    Excel 2010
    Posts
    41

    Re: Using a formula to calcualte different mileage rates

    If(c6<10000,c6*0.45,c6*0.25)
    STOP! CONTINENTAL DRIFT.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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