# 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

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

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

Replies
0
Views
209
Replies
10
Views
290
Replies
6
Views
461
Replies
25
Views
1K
Replies
8
Views
248

1,171,700
Messages
5,877,014
Members
433,228
Latest member
Sarah1989

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