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

    More Formula (2000)

    I think it's time for the weekend, I really can't think straight. More help is appreciated please. Have two threshololds say 100, and 120. For savings a client has between 100 and 120 he gets a 50% bonus so someone with 110 gets an extra 5.00 to give them 115. However if someone has an amount above the 120 threshold he loses25% for anything above it but only to the extent it eliminates any previous earned bonus.

    So someone with 180 will get a 10 bonus for the 50% bonus between 100 and 120 but lose 60 * 25% = 15 for the 60 above the 120 theshold, this is more than the earned bonus so I need this capped at 10. Again any formula idea is greatly appreciated.


    Kiond regards Darren

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

    Re: More Formula (2000)

    There are probably more efficient formulas for this, but here is one. Replace D1 with the cell containing the savings amount.

    =D1+50%*MAX(MIN(D1-100,120-100),0)-MIN(50%*MAX(MIN(D1-100,120-100),0),25%*MAX(D1-120,0))

    You can replace 100, 120, 50% and 25% with cell references, of course.

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

    Re: More Formula (2000)

    Thanks again Hans.

    Darren.

Posting Permissions

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