# Make this formula easier

#### DavidAC

##### Board Regular
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

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

#### Greg Truby

##### MrExcel MVP
=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

##### Active Member
=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

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

#### DavidAC

##### Board Regular
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

##### MrExcel MVP
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.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,167,961
Messages
5,856,531
Members
431,819
Latest member
Tori Murphy

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back