anyone care to tackle this??

[fivermsg]

this is the formula i came up with for stats we have to keep were i
work.. the run down is that as your percentage increase there is a
higher payout.

Can this be made smaller?

=IF(A1="French
Stats",($E$10<100%)*(2.5*E6)+($E$10>99.99%)*($E$10<125%)*(3.5*E6)+($E$10>124.99%)*($E$10<150%)*(4*E6)+($E$10>149.99%)*($E$10<175%)*(4.5*E6)+($E$10>174.99%)*($E$10<200%)*(4.75*E6)+($E$10>199.99%)*($E$10<225%)*(5*E6)+($E$10>224.99%)*($E$10<250%)*(5.25*E6)+($E$10>249.99%)*($E$10<275%)*(5.5*E6)+($E$10>274.99%)*($E$10<300%)*(5.75*E6)+($E$10>299.99%)*($E$10<325%)*(6*E6)+($E$10>324.99%)*($E$10<350%)*(6.25*E6)+($E$10>349.99%)*($E$10<375%)*(6.5*E6)+($E$10>374.99%)*($E$10<400%)*(6.75*E6))

as you can see there is a lot of between % (i.e paid this between
100-125%)

 

[Niek Otten]

Take a look at this:

http://www.mcgimpsey.com/excel/variablerate.html

 

[Doug Kanter]

A quick glance suggests that it might be possible to use a lookup table to
pick up the necessary results. Maybe.

 

[Bill Martin]

At best you're going to end up with a formula that is very difficult to
understand and change later. At worst you'll end up with something that
works and provides wrong answers without realizing it.

If it were me, I'd change direction and create a simple table with one
column having your % thresholds, and a second column with the multipliers.
Then create a simple formula using VLOOKUP() to lookup your E10 value in the
table and return the desired multiplier for E6.

Go into the Excel help system and ask it to tell you about VLOOKUP.

Good luck...

Bill