+ Reply to Thread
Results 1 to 3 of 3

Multiple condition formula help please.

  1. #1
    Registered User
    Join Date
    09-24-2012
    Location
    NJ, USA
    MS-Off Ver
    Excel 2003
    Posts
    5

    Multiple condition formula help please.

    I have to come up with a formula that meets the following conditions.

    The formula is to calculate the cost of maintenance on a car. The cost of maintenance is .0375 cents per mile for the first 10,000 miles the car is driven per year, .045 cents per mile for each mile driven after those first 10,000 miles. Also the minimum maintenance cost is $300, meaning that if the car is not driven at all there should still be a maintenance cost of $300.

    Any help is appreciated. Thanks

  2. #2
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Multiple condition formula help please.

    maybe
    A1= total miles run

    =MAX(300,IF(A1<=10000,A1*0.0375,A1*0.045))
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  3. #3
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Multiple condition formula help please.

    The cost of maintenance is .0375 cents per mile for the first 10,000 miles the car is driven per year, .045 cents per mile for each mile driven after those first 10,000 miles.
    Its only the miles above 10000 that need to be calculated at 0.045. Hence,

    =MAX(300,IF(A1<=10000,A1*0.0375,375+(A1-10000)*0.045))
    or
    =MAX(300,SUMPRODUCT((A1>{0,10000})*(A1-{0,10000})*{0.0375,0.0075}))
    Life's a spreadsheet, Excel!
    Say thanks, Click *

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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