anyone care to tackle this??
 

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$1099.99%)*($E$10 <125%)*(3.5*E6)+($E$10124.99%)*($E$10<150%)*(4*E6 )+($E$10149.99%)*($E$10<175%)*(4.5*E6)+($E$10174 .99%)*($E$10<200%)*(4.75*E6)+($E$10199.99%)*($E$1 0<225%)*(5*E6)+($E$10224.99%)*($E$10<250%)*(5.25* E6)+($E$10249.99%)*($E$10<275%)*(5.5*E6)+($E$102 74.99%)*($E$10<300%)*(5.75*E6)+($E$10299.99%)*($E $10<325%)*(6*E6)+($E$10324.99%)*($E$10<350%)*(6.2 5*E6)+($E$10349.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%)


--
fivermsg
------------------------------------------------------------------------
fivermsg's Profile: http://www.excelforum.com/member.php...o&userid=32348
View this thread: http://www.excelforum.com/showthread...hreadid=521162

anyone care to tackle this??
 

Take a look at this:

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


--
Kind regards,

Niek Otten

"fivermsg" wrote in message
...

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$1099.99%)*($E$10 <125%)*(3.5*E6)+($E$10124.99%)*($E$10<150%)*(4*E6 )+($E$10149.99%)*($E$10<175%)*(4.5*E6)+($E$10174 .99%)*($E$10<200%)*(4.75*E6)+($E$10199.99%)*($E$1 0<225%)*(5*E6)+($E$10224.99%)*($E$10<250%)*(5.25* E6)+($E$10249.99%)*($E$10<275%)*(5.5*E6)+($E$102 74.99%)*($E$10<300%)*(5.75*E6)+($E$10299.99%)*($E $10<325%)*(6*E6)+($E$10324.99%)*($E$10<350%)*(6.2 5*E6)+($E$10349.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%)


--
fivermsg
------------------------------------------------------------------------
fivermsg's Profile: http://www.excelforum.com/member.php...o&userid=32348
View this thread: http://www.excelforum.com/showthread...hreadid=521162

anyone care to tackle this??
 

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

"fivermsg" wrote in
message ...

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$1099.99%)*($E$10 <125%)*(3.5*E6)+($E$10124.99%)*($E$10<150%)*(4*E6 )+($E$10149.99%)*($E$10<175%)*(4.5*E6)+($E$10174 .99%)*($E$10<200%)*(4.75*E6)+($E$10199.99%)*($E$1 0<225%)*(5*E6)+($E$10224.99%)*($E$10<250%)*(5.25* E6)+($E$10249.99%)*($E$10<275%)*(5.5*E6)+($E$102 74.99%)*($E$10<300%)*(5.75*E6)+($E$10299.99%)*($E $10<325%)*(6*E6)+($E$10324.99%)*($E$10<350%)*(6.2 5*E6)+($E$10349.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%)


--
fivermsg
------------------------------------------------------------------------
fivermsg's Profile:
http://www.excelforum.com/member.php...o&userid=32348
View this thread: http://www.excelforum.com/showthread...hreadid=521162

anyone care to tackle this??
 

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
-----------------------------
"fivermsg" wrote in
message ...

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$1099.99%)*($E$10 <125%)*(3.5*E6)+($E$10124.99%)*($E$10<150%)*(4*E6 )+($E$10149.99%)*($E$10<175%)*(4.5*E6)+($E$10174 .99%)*($E$10<200%)*(4.75*E6)+($E$10199.99%)*($E$1 0<225%)*(5*E6)+($E$10224.99%)*($E$10<250%)*(5.25* E6)+($E$10249.99%)*($E$10<275%)*(5.5*E6)+($E$102 74.99%)*($E$10<300%)*(5.75*E6)+($E$10299.99%)*($E $10<325%)*(6*E6)+($E$10324.99%)*($E$10<350%)*(6.2 5*E6)+($E$10349.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%)


--
fivermsg
------------------------------------------------------------------------
fivermsg's Profile:
http://www.excelforum.com/member.php...o&userid=32348
View this thread: http://www.excelforum.com/showthread...hreadid=521162