Make this formula easier

[DavidAC]

Hi, i have just inherited a spreadsheet to work out bonuses etc for staff however the formula appears to be very complex for what it trys to do is there a way i can make it easier to write. the formula is
=IF((I3+I4)/(SUM(F3,G3,F4,G4)/7.4)>=14,0.5,0)+IF((I3+I4)/(SUM(F3,G3,F4,G4)/7.4)>=14.5,2)+IF((I3+I4)/(SUM(F3,G3,F4,G4)/7.4)>=15,1.5)+IF((I3+I4)/(SUM(F3,G3,F4,G4)/7.4)>=15.5,1.5)+IF((I3+I4)/(SUM(F3,G3,F4,G4)/7.4)>=16,3)+IF((I3+I4)/(SUM(F3,G3,F4,G4)/7.4)>=16.5,3)+IF((I3+I4)/(SUM(F3,G3,F4,G4)/7.4)>=17,3)+IF((I3+I4)/(SUM(F3,G3,F4,G4)/7.4)>=17.5,3)+IF((I3+I4)/(SUM(F3,G3,F4,G4)/7.4)>=18,3)+IF((I3+I4)/(SUM(F3,G3,F4,G4)/7.4)>=18.5,3)+IF((I3+I4)/(SUM(F3,G3,F4,G4)/7.4)>=19,3)+IF((I3+I4)/(SUM(F3,G3,F4,G4)/7.4)>=19.5,3)+IF((I3+I4)/(SUM(F3,G3,F4,G4)/7.4)>=20,3)

Regards

 

[Greg Truby]

=CHOOSE(MATCH((I3+I4)/(SUM(F3,G3,F4,G4)/7.4),{0,14,14.5,15,15.5,16,16.5,17,17.5,18,18.5,19,19.5,20},1),0,0.5,2.5,4,5.5,8.5,11.5,14.5,17.5,20.5,23.5,26.5,29.5,32.5)

HTH

 

[north19701]

=VLOOKUP((I3+I4)/(SUM(F3:G4)/7.4),{0,0;14,0.5;14.5,2.5;15,4;15.5,5.5;16,8.5;16.5,11.5;17,14.5;17.5,17.5;18,20.5;18.5,23.5;19,26.5;19.5,29.5;20,32.5},2,1)

May want to check the math, but here's a go at it.

 

[DavidAC]

Thanks for your help it worked great and a lot quicker as well, your help is appreciated

 

[DavidAC]

Choose/Lookup formla

I spoke to soon with regards to the formula working correctly, there seems to be a problem when copying the formula down past the 3rd line as it returns a zero answer even though the data is the same as the previous lines. the formala used is
=CHOOSE(MATCH((I3+I4)/(SUM(F3:G4)/7.4),{0,14,14.5,15,15.5,16,16.5,17,17.5,18,18.5,19,19.5,20},1),0,0.5,2.5,4,5.5,8.5,11.5,14.5,17.5,20.5,23.5,26.5,29.5,32.5).

help its driving me nutty

 

[Greg Truby]

Sounds like one of the references needs to be made absolute. Try going to the cell where it's failing and hitting F2 and looking at the colored boxes that show where the formula is now referencing. Are any of these now mis-located? If so, note which ones drifted that shouldn't, then go back to your original formula, hit F2 and then put the cursor inside each reference that needs to be absolute and hit your F4 key. You should see the reference change to include dollar signs, e.g. [G4] would become [$G$4] or [I3] would become [$I$3]. After you've locked (made absolute) those reference that should not drift, you should be able to copy the formula down.