Software

Ask something
Discussion Locked

Excel IF Formula

0 helpful
7 replies

by lf king

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I'm tracking employee performance in excel and need help with a formula. The formula inserted is:
=IF(H9=30000,"15",IF(H9=60000,"30",IF(H9=90000,"45",IF(H9=120000,"60",IF(H9=150000,"75",IF(H9=180000,"90","No Yards Gained"))))))

H9 contains a formula that sums H4:H8. If that total reaches the breaks above the team gets the points.

The numbers refer to dollar amounts of new accounts- each employee that opens a new account helps the team to make yards.

The formula works fine if I enter 10,000, 20,000 30,000 etc. But if I enter numbers like 2,224, 9,874 (which is real world)it doesn't put in points.

Please Help

7 replies

by bughunter

Change the equal signs to greater than, less than, greater than or equal to, etc. as appropriate.

by lf king

I've tried different combinations and it doesn't go past 15 points for any level of money.

by dklippert

How's this.
Set up a little table:

30000 15
60000 30
90000 45
120000 60
150000 75
180000 90

Select the table and go to Insert>Name>Define. Call it Scores.

Now use this formula:

=IF(H9<30000,"No yards gained",VLOOKUP(H9,Scores,2))

by theresa.l.richardson

Linda you will need to use an IF(AND statement.

=IF(H9<30000,"Missed target",(IF(AND(H9>=30000,H9<60000),"15",(IF(AND(H9>=60000,H9<90000),"30",(IF(AND(H9>=90000,H9<120000),"45",(IF(AND(H9>=120000,H9<150000),"60",(IF(AND(H9>=150000,H9<180000),"75",(IF(AND(H9>=180000),"90","No Yards Gained")))))))))))))

This discussion is closed to new replies.