+ Reply to Thread
Results 1 to 6 of 6

Conditional Multiplier

  1. #1
    Registered User
    Join Date
    02-24-2010
    Location
    Bordon
    MS-Off Ver
    Excel 2007
    Posts
    3

    Question Conditional Multiplier

    Hey guys,
    I really need some help.
    I am trying to create a document that will make claiming mileage easier.
    The gov have a rule now that you can claim 40p a mile for the first 10,000 miles and 25p therafter. But you have to 'log' each trip seperately.
    So I have created a sheet that calculates overall mileage based on each trip entered and puts the total into a cell (lets call it cell 'a'). I have done this by doing a simple column add.

    What I want to do is have a cell for each trip made that shows the amount of miles travelled multilied by either 40p or 25p depending on if the total mileage is less than or more than 10,000 miles.

    i.e. Target cell has a formula that says if cell 'a' is less than 10,000 the mileage entered in cell 'b' is multiplied by .4 to give a monetary value OR if cell 'a' is greater than 10,000 then the mileage entered in cell 'b' is muliplied by 25p to give a monetary value
    Last edited by DonkeyOte; 02-24-2010 at 05:47 AM.

  2. #2
    Valued Forum Contributor JeanRage's Avatar
    Join Date
    03-02-2009
    Location
    Nice, France
    MS-Off Ver
    Excel 2003
    Posts
    705

    Re: Help with 'IF' formula please

    Hi,

    See attached sample ...

    HTH
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    02-24-2010
    Location
    Bordon
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Help with 'IF' formula please

    Many thanks HTH

  4. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Conditional Multiplier

    i think thats wrong! that formula calculates every thing at 25p once over 10000
    so 19,000
    returns £4,750

    since its 40p up to 10000 then 25p there after surely it should be
    £6250
    10000@ 40p =4000
    9000@25p=2250

    =MAX((A2-10000)*0.25,0)+MIN(A2,10000)*0.4
    otherwise if you did 10001 miles youd get £2,500.25
    which is less than the 4000 for 10000 !
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  5. #5
    Valued Forum Contributor JeanRage's Avatar
    Join Date
    03-02-2009
    Location
    Nice, France
    MS-Off Ver
    Excel 2003
    Posts
    705

    Re: Conditional Multiplier

    Hi,

    Thanks to martin ... since I misunderstood the question ...

    HTH
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    07-07-2014
    Location
    Dubai
    MS-Off Ver
    2007
    Posts
    1

    Re: Conditional Multiplier

    Thanks man, you have saved us a lots of time.


    bundles of thanks.

+ 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