Results 1 to 4 of 4

Thread: Formula (2000)

  1. #1
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formula (2000)

    This should be easy but my mind is fried after 6 or 7 hours on a spreadsheet. I need a formula to say if A and B exceed C then multiply the excess over C by x% but cap it at D. So lets say 10, 20, 25, 50 where 10 + 20 (A + exceed 25 so multiply the excess of 5 by the given % say 0.6. However lets say (A) + ( was 55, this is 5 over (D)50, here I would only want 25 * 0.6, the excess over C but capped at D. Any hints would be great.

    Many thanks Darren

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula (2000)

    Try <code>=MAX(MIN(A+B,D)-C,0)*x</code>

  3. #3
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula (2000)

    =IF((A1+B1)>D1,C1*0.6,IF((A1+B1)>C1,((A1+B1)-C1)*0.6,0))

  4. #4
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula (2000)

    Thank you hans, as ever than is fantastic.

Posting Permissions

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