1. ## 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. ## 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. ## 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
•