Jul 29 2021 08:22 AM
I am trying to write an IF formula in excel that has multiple conditions from multiple cells.
Columns C, D, E, & F are conditional formatted so that if the value in each of them is <300 the cell fills with a light green color, if the value is between 301-700 the cell fills with a yellow color, and if the value is >700 the cell fills with an orange color. Green fill color indicates the count is low, yellow fill color indicates the count is medium, and orange fill color indicates the count is high.
The Column I am trying to write the IF formula into is Column G and I need it to put either the letter N or the letter Y (for No or Yes) into the cells of Column G depending on what the values in Columns C-F are. If all 4 cells (C-F) are each <300 then the cells in Column G should be N
If at least 2 cells (C-F) are >300 then the cells in Column G should be Y
I have written both an IF(AND) formula: IF((AND(C11>300,D11>300,E11>300,F11>300)),"Y","N")
as well as an IF(OR) formula: IF((OR(C13>300,D13>300,E13>300,F13>300)),"Y","N")
The IF(AND) formula works correctly at putting the N value into the cells of Column G only if one or none of the cells (C-F) is above 300, and will correctly put the Y value into the cells of Column G only if all 4 cells (C-F) are above 300. However, if 2 or 3 of the cells (C-F) are above 300, it incorrectly puts the letter N by using the IF(AND) formula as it is written.
The IF(OR) formula works correctly at putting the Y value into the cells of Column G if two or more of the cells (C-F) are above 300 as well as correctly putting the N value into the cells of Column G only if all 4 cells (C-F) are below 300. However, if only one of the cells (C-F) are above 300, then if incorrectly puts the letter Y instead of the letter N by using the IF(OR) formula as it is written.
Can someone help me figure out how to correct this so that I only have to use one formula for all the cells in Column G?
Jul 29 2021 08:27 AM
Jul 29 2021 08:47 AM
Jul 29 2021 12:41 PM
Jul 29 2021 12:56 PM
I don't understand either...in your example, it works, but when I applied it to mine, it did not.
Check out the Screenshot I took.
Jul 29 2021 01:19 PM
Could you attach a copy of your workbook instead of a screenshot? You can delete everything that is not relevant to the problem.
Jul 29 2021 01:54 PM
Jul 29 2021 02:14 PM
SolutionThanks. In the sample workbook in your first post in this thread, the data began in row 10, so I posted a formula for row 10.
But in your 'real' workbook, the data begin in row 11, but you still used the formula for row 10. Hence the result referred to the wrong row.
See the attached version.
Jul 30 2021 08:19 AM
Jul 29 2021 02:14 PM
SolutionThanks. In the sample workbook in your first post in this thread, the data began in row 10, so I posted a formula for row 10.
But in your 'real' workbook, the data begin in row 11, but you still used the formula for row 10. Hence the result referred to the wrong row.
See the attached version.