• Status: Solved
• Priority: Medium
• Security: Public
• Views: 193

# Excel 2008 Nested Formula Assistance

Hello,

Looking for formula assistance to do the following based on the attached sample:
1.  If "Pass" in column A, then status in column H shows "Pending"
2.  If "Decline" in columns D or E, then status in column H shows "Decline"
3.  If score in column I is >3, then status in column H shows "Pass"
4.  If score in column I is <3, then status in column H shows "Fail"
5.  If there is no data in any of these columns, then the status in column H shows blank.

Thanks!
EE-Sample.xlsx
0
Escanaba
• 2
• 2
1 Solution

Commented:
I assume those need to be applied in the order shown (so if more than one is true the first one in order takes precedence). In which case try this formula in H2

=IF(A2="Pass","Pending",IF(OR(D2="Decline",E2="Decline"),"Decline",IF(I2>3,"Pass",IF(ISNUMBER(I2),"Fail",""))))

regards, barry
0

Author Commented:
Thanks, Barry.  The problem I'm running into is when changes are made to the worksheet.  So when I paste your formula in, the status shows "Pending" which is correct.  Once the end-user types in a final score in column I, lets say a 4, the status should change to "Pass" but its staying at "Pending".

The same goes for if the end-user enters "Decline" in columns D or E.  The status should change from "Pending" to "Decline".
0

Commented:
OK, it's just a case of changing the order. Assuming you want Pass/Fail if there's a score and that takes precedence over everything else then try this version

=IF(I2>3,"Pass",IF(ISNUMBER(I2),"Fail",IF(OR(D2="Decline",E2="Decline"),"Decline",IF(A2="Pass","Pending",""))))

regards, barry
0

Author Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.