anyone care to tackle this??

F

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%)
 
D

Doug Kanter

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top