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

Take a look at this:

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


--
Kind regards,

Niek Otten

"fivermsg" <[email protected]> wrote in message
news:[email protected]...
>
> 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%)
>
>
> --
> fivermsg
> ------------------------------------------------------------------------
> fivermsg's Profile: http://www.excelforum.com/member.php...o&userid=32348
> View this thread: http://www.excelforum.com/showthread...hreadid=521162
>

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

"fivermsg" <[email protected]> wrote in
message news:[email protected]...
>
> 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%)
>
>
> --
> fivermsg
> ------------------------------------------------------------------------
> fivermsg's Profile:
> http://www.excelforum.com/member.php...o&userid=32348
> View this thread: http://www.excelforum.com/showthread...hreadid=521162
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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%)
>
>
> --
> fivermsg
> ------------------------------------------------------------------------
> fivermsg's Profile:
> http://www.excelforum.com/member.php...o&userid=32348
> View this thread: http://www.excelforum.com/showthread...hreadid=521162
>