Make this formula easier

DavidAC

Board Regular
Joined
Feb 10, 2003
Messages
127
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)
:cry:
Regards
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
=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
Joined
Jun 17, 2003
Messages
491
=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
Joined
Feb 10, 2003
Messages
127
Thanks for your help it worked great and a lot quicker as well, your help is appreciated
 

DavidAC

Board Regular
Joined
Feb 10, 2003
Messages
127
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
Joined
Jun 19, 2002
Messages
10,014
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,521
Messages
5,596,653
Members
414,083
Latest member
Mrsash

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

Which adblocker are you using?

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
Top