Sliding scale formula

**vbidiot** · 05-11-2009, 07:59 PM

Hello All...

I always have trouble with the sliding scale formulas with if statements so any help would be greatly appreciated!

Basically, the formula calls for "if goal is x% then payout is Y"

If goal is Pays
80% 7.5
95% 8.25
100% 15.00
103% 15.90
110% 18.00
115% 75.00

I'm having problems calculating the sliding scale portion of the formula. For example, if the goal is 83% then calculate the sliding scale portion based on overall.

**shg** · 05-11-2009, 08:10 PM

=lookup(a1, {0.8,0.95,1,1.03,1.15},{7.5,8.25,15,15.9,18,75})

**Chance2** · 05-11-2009, 08:20 PM

SHG,

I think you missed a value in your formula:

=LOOKUP(D1, {0.8,0.95,1,1.03,1.1,1.15},{7.5,8.25,15,15.9,18,75})

As an alternative (not as nice) you could nest ifs:

=IF(A1>1.14,75,IF(A1>1.09,18,IF(A1>1.02,15.9,IF(A1>0.99,15,IF(A1>0.94,8.25,IF(A1>0.79,7.5,0))))))

**vbidiot** · 05-12-2009, 09:13 AM

The above works fine but doesn't take into consideration of the numbers in-between. For example, if the number is 83%, then the payout should be a number between 7.5 and 8.25.

**zbor** · 05-12-2009, 09:27 AM

Try this:

Book1(1).xls

Won't work for less than 0,80

**vbidiot** · 05-13-2009, 12:28 PM

Thanks ...works perfectly

**MartinGTC** · 07-11-2011, 10:17 AM

Following on from this, I have a quick question.

I have figures such as 1, 1.5, 2, 2.5, 3...with set figures relating to each (63, 80, 98, 115, 136) but using the above formula - the trend doesnt match up following through the figures....can anyone help?!