Multiple "IF" commands with different formulas calling from a Table.

Need some help with this one..

I need the Agent portion to update with the Plan ID and use the formula that corresponds to the Plan. This is only a partial list of the PLans. (I have 23) I want to managers to put in the PLAN ID and it looks up the proper formula and uses the Gross REV number to give the portion that is paid to the sales person..

I hope I am explaining this properly. I have attached the file in 2007 and 97-2003 versions...

If there is an easier way to do this please do tell.

Help.xls

Help.xlsx


Thanks,

Waylander

You can add a helper column to make it easier

I22=IF($J$22>=H22,MIN(H22-H21,$J$22-H21),"")

THEN COPY DOWN
then you can use sumproduct to get the agent portion

I do not know if it helps you, but I reduced your formula

=IF(I22<30000,I22*50%,15000)+IF(I22>65000,21000,IF(I22>30000,(I22-30000)*60%,0))+IF(I22>85000,14000,IF(I22>65000,(I22-65000)*70%,0))+IF(I22>100000,11250,IF(I22>85000,(I22-85000)*0.75,0))+IF(I22>100000,(I22-100000)*0.9,0)

into

=SUMPRODUCT(IF($I$22>F22:F26,(IF(I22>H22:H26,H22:H26,I22)-F22:F26)*E22:E26))

See the attachement