Maximum nested IF functions reached, Need shorter formula

Hello everyone,

I am currently trying to create a time sheet in which names can be associated with pay rates so it can automatically work out pay.

The five headings are 'Operative, standard (as in standard pay), time+1/4, time+1/2 & Pay' and under each pay heading I want to be able to input the number of hours they have worked and for them to correspond with the pay rate of the 'operative', adjusting the pay accordingly.

The formula I created worked for 3 members of staff however when i tried to expand it to 25 members of staff it would not let me (the formula used is below).

=IF(C3 = K$7,(E3*L$7)+((F3*L$7)+(F3*(L$7/4)))+((G3*L$7)+(G3*(L$7/2))),IF(C3 = K$8,(E3*L$8)+((F3*L$8)+(F3*(L$8/4)))+((G3*L$8)+(G3*(L$8/2))),IF(C3 = K$9,(E3*L$9)+((F3*L$9)+(F3*(L$9/4)))+((G3*L$9)+(G3*(L$9/2))))))

I have 4 different pay rates and 25 members of staff, is there another was to do this so the formula is shorter? FYI I am using Excel '07.

I hope this makes sense

Carl

It's hard to be sure without seeing your actual sheet, but maybe something like:

=(E3*OFFSET(L$6,MATCH(C3,$K$7:$K$9,0),0))+(F3*(OFFSET(L$6,MATCH(C3,$K$7:$K$9,0),0)*1.25))+(G3*(OFFSET(L$6,MATCH(C3,$K$7:$K$9,0),0)*1.5)

NBVC's fix seems to have worked, I will confirm when I have made sure though.

Thank you both for the resposes, very much appreciated!

Carl

NBVC's Fix worked, just had to change the last VLOOKUP as it was calculating 1/2 time rather than time + 1/2.

Thanks again!



Carl