Help with Excel Formula (IF OR with 3 conditions)

[jintang]

Hi

I would require assistance with the formula that I am currently working on. This formula is used in the summary page and it would reflect these 3 conditions
1. If the cell is empty, it will show "Not Completed"
2. If the cell is filled up, it will show "Done"
3. If the cell is highlighted in red (from conditional formatting), it will reflect "error" --> Conditional formatting set that if value is not within the range, it will be highlighted red.

Currently, I am able to use the IF OR formula for Condition 1 and 2, but I am stuck with Condition 3. Would like to seek help inserting this into my formula.

This is the formula that I used
=IF((OR('Daily BsC'!F6:F8="",'Daily BsC'!F10:F12="",'Daily BsC'!F14:F20="")),"Not Completed","Done")

Thanks.

Regards
Jin Tang

 

[Michael M]

You can't use a formula to identify a cell colour in that way.
You would need to write code to do that

 

[jintang]

how should i go about on writing the code? could you advice?

 

[Crystalyzer]

You would have to use the formula you use in the conditional formatting in your formula. I don't know what you are using, but assuming a range of values I did the following two formulas

Using "IFS" function

=IFS(OR('Daily BsC'!F6:F8="",'Daily BsC'!F10:F12="",'Daily BsC'!F14:F20=""),"Not Completed",AND(AND('Daily BsC'!F6:F8>=0,'Daily BsC'!F6:F8<=5),AND('Daily BsC'!F10:F12>=6,'Daily BsC'!F10:F12<=10),AND('Daily BsC'!F14:F20>=11,'Daily BsC'!F14:F20<=25)),"Done",TRUE,"Error")

Using nested "IF"

=IF(NOT(OR('Daily BsC'!F6:F8="",'Daily BsC'!F10:F12="",'Daily BsC'!F14:F20="")),IF(AND(AND('Daily BsC'!F6:F8>=0,'Daily BsC'!F6:F8<=5),AND('Daily BsC'!F10:F12>=6,'Daily BsC'!F10:F12<=10),AND('Daily BsC'!F14:F20>=11,'Daily BsC'!F14:F20<=25)),"Done","Error"),"Not Completed")

 

[jintang]

ahhh , understood. Thanks! will try that out

 

[Crystalyzer]

You're welcome, happy to help!

 

[jintang]

HI, I was trying out using nested "IF" and it returns me #value! in the output.

currently using excel 2013, so IFs doesn't work for me.

 

[Crystalyzer]

Can you post a copy of your worksheet with the error using the XL2BB tool? Is almost impossible to troubleshoot on the limited information you've provided.

 

[jintang]

Thanks for reaching out, however, the code worked for my current excel 2016.
The error only occurs when I am using excel 2013 on the other laptop.
Attached is the screenshot. As seen, there is no error, but on my excel 2013 on the laptop, it will show #value.

I am unable to find XL2BB on my add-ins so i can only provide you with the screenshot

 

[Crystalyzer]

Try entering it as an array formula using CTL-SHFT-ENTER