# 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

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

#### 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
293
Replies
15
Views
182
Replies
6
Views
469
Replies
25
Views
1K

Threads
1,172,219
Messages
5,879,758
Members
433,454
Latest member
xWiZardx

### 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.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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