How do I return specific results based on information in other cells.

Hello -

I am creating an attendance tracker for work but currently I am having brain freeze and can figure out how to create a formula that tells me the following.

In Column D - I want it state, "Review of Expectations", "PIP Required" or just be "blank" based on information in Column AU as well as Columns E,F and G.

If Employee 1 has 2.5 in column AU15 then "Review of Expectations" should appear in Column D15. Once the Supervisor selects "YES" in Cell E15 then Cell D15 should return to blank.

If Employee 1 has 3 in Column AU15 then "Verbal Required" should display in D15 until the supervisor inputs "YES" in Cell E15 then Cell D15 will return to Blank.

If Employee 1 has 4 in column AU15 then, "Written Required" should display in D15 until the supervisor inputs "YES" in Cell F15 then Cell D15 will return to blank.

Finally, If employee 1 has 6 in column AU15 then, "Final Required" should display in D15 until the supervisor inputs "Yes" in cell G15 then cell D15 will return to blank.

Please review the attached and give me some assistance.

If asked for a Password, please input "MoonPie".

Thanks,

[D15] =IF(E15="",IF(F15="",IF(G15="",CHOOSE(MIN(INT(AU15)+1,7),"","","Review of Expectations","Verbal Required","Written Required","Final Required","Unknown Value in AU"&ROW()),""),IF(AND(AU15=6,G15=""),"Final Required",IF(AU15=3,"PIP Required",""))),"")

If AU15 is 7 or more, it generates the message "Unknown Value in AU15".

Thank you - This is certainly in the correct direction.

The only difference is, with the above formula, putting "YES" in any of the 3 cells will clear D15. I only need it to clear based on the warranted action.

If "Verbal" is recommended, then the only action that would clear "Verbal in cell D15 would be by placing "YES" in cell E15. If Written is recommended, then "YES" would need to be selected in cell F15 and the same for Final and using G15.

May have to be multiple IF statements but I am an excel novice.

Thanks for the assistance.

Unfortunately, this formula is only looking for the required documentation but does not clear the cell once the "YES" indicator has been placed in Cells E,F and G.

Originally Posted by leelnich

=IF(AU15<=2.5,IF(E15="YES","","Review of Expectations"),IF(AU15<=3.5,IF(E15="YES","","Verbal Required"),IF(AU15<=4,IF(F15="YES","","Written Required"),IF(AU15<=6,IF(G15="YES","","Final Required")))))

Originally Posted by leelnich

You can clearly see the "YES" tests in the formula. Please describe completely the range of values in AU15. Is it ever blank, and if so, what to return? Do the values ever exceed 6? Are the given values exact matches or just endpoints to establish ranges?

Let me see if I can clear up my original post.

Column D is a "Pending Action" column which indicates what level of corrective action an employee should receive based on total number of 'points' in Column AU. If an employee has 2.0 to 2.9 in Column AU then Column D should populate "Review of Expectations". Once this conversation has been had the Sup/Manager will select "Yes" in column E at which time Column D should return to blank.

When an employee has 3.0 to 3.9 points in column AU then Column D should populate "Verbal Required". Once the sup/manager has the conversation and selects "Yes" in column F then column D should return to 'blank'.

When an employee has 4.0 to 4.9 in column AU then column D should populate "Written Required". Once the Sup/mgr has this conversation and Selects 'Yes' in column F then Column D should return to Blank.

Finally, if Column Au has 5+ points, then Column D should populate "Final Required". Once the Sup/Mgr has this conversation and selects 'YES" in column G then Column D should return to Blank..

I hope this helps.

Originally Posted by leelnich

This sets ranges from the opposite end, perhaps it suits:
=IF(AU15>=6,IF(G15="YES","","Final Required"),IF(AU15>=4,IF(F15="YES","","Written Required"),IF(AU15>=3.5,IF(E15="YES","","Verbal Required"),IF(AU15>=2.5,IF(E15="YES","","Review of Expectations"),"UNKNOWN"))))

AU15 as posted equals 4.5. What is the correct returned value for that in D15, assuming no "YES"?

Let me see if I can clear up my original post.

Column D is a "Pending Action" column which indicates what level of corrective action an employee should receive based on total number of 'points' in Column AU. If an employee has 2.0 to 2.9 in Column AU then Column D should populate "Review of Expectations". Once this conversation has been had the Sup/Manager will select "Yes" in column E at which time Column D should return to blank.

When an employee has 3.0 to 3.9 points in column AU then Column D should populate "Verbal Required". Once the sup/manager has the conversation and selects "Yes" in column F then column D should return to 'blank'.

When an employee has 4.0 to 4.9 in column AU then column D should populate "Written Required". Once the Sup/mgr has this conversation and Selects 'Yes' in column F then Column D should return to Blank.

Finally, if Column Au has 5+ points, then Column D should populate "Final Required". Once the Sup/Mgr has this conversation and selects 'YES" in column G then Column D should return to Blank..

I hope this helps.