Conditional Formatting

[Anny]

Hi,
I am newly join as member and please help me to create conditional formatting by use formula for monthly linearity %(eg. 33.3% per month in a Quarter). Please refer attach file

Thanks

 

[AliGW]

Is this an OR condition or an AND condition? Does the cell turn red if only two out of three meet the conditions?

 

[Anny]

I think should be And condition.
- When month 1 fill (month 2 & 3 are blank), cell turn red if <33.3%.
- When month 1 & 2 fill (month 3 is black), cell turn red if <66.6%
- When all 3 months fill, cell turn red if <99.9%

Thanks

 

[AliGW]

Select cell F3.

Set a formula CF rule using this formula:

=OR(AND(C1="",D1="",B1/A1<0.333),AND(B1<>"",D1="",(B1+C1)/A1<0.666),AND(B1<>"",C1<>"",(B1+C1+D1)/A1<0.999))

Set the applies to range to =$F:$F

 

[Anny]

It's not turn to the result I want. When I only input month 1 at 3, result 45%, but it turn red. Even I input month 1 & 2 (total 90%), result turn red as well.

 

[AliGW]

Sorry! I missed a criterion. Try this instead:

=OR(AND(B3<>"",C3="",D3="",B3/A3<0.333),AND(B3<>"",C3<>"",D3="",(B3+C3)/A3<0.666),AND(B3<>"",C3<>"",D3<>"",(B3+C3+D3)/A3<0.999))

 

[Haz]

=F3<CHOOSE(COUNT(B3:D3),1/3,2/3,1)

 

[Anny]

Sorry! I missed a criterion. Try this instead:

=OR(AND(B3<>"",C3="",D3="",B3/A3<0.333),AND(B3<>"",C3<>"",D3="",(B3+C3)/A3<0.666),AND(B3<>"",C3<>"",D3<>"",(B3+C3+D3)/A3<0.999))

Thanks Ali, this is working.

 

[Anny]

=F3<CHOOSE(COUNT(B3:D3),1/3,2/3,1)

Thanks Haz, This is simple formula and working as well.

 

[bosco_yip]

Maybe,

=F3<COUNT(B3:D3)/3

Regards
Bosco