Conditional Formatting with AND() not working when TRUE

[bbkdude]

Hello,

I am looking for some help as to why my AND() conditional formatting does not work. I have an expandable data set (which is why K4 is an Offset function).

The K4 formula will always average the previous 5 yrs worth of data.

When I have conditional formatting for the Quarters, I want it to highlight when above the 5 year quarterly average (the SUM() in K4) BUT only if the quarter is within the past 5 years.

The Greater Than conditional formatting that I got to work is:

=B7>SUM(OFFSET($K$3,4,-6,1,5),OFFSET($K$3,8,-6,1,5),OFFSET($K$3,12,-6,1,5),OFFSET($K$3,16,-6,1,5))/((COUNTIF(OFFSET($K$3,4,-6,1,5),"<>0")+(COUNTIF(OFFSET($K$3,8,-6,1,5),"<>0")+(COUNTIF(OFFSET($K$3,12,-6,1,5),"<>0")+(COUNTIF(OFFSET($K$3,16,-6,1,5),"<>0"))))))

for the range: =$B$7:$I$7,$B$11:$I$11,$B$15:$I$15,$B$19:$I$19

When I try and add the AND() part to also have it work for being within the past 5 years, it fails.

=AND(B7>SUM(OFFSET($K$3,4,-6,1,5),OFFSET($K$3,8,-6,1,5),OFFSET($K$3,12,-6,1,5),OFFSET($K$3,16,-6,1,5))/((COUNTIF(OFFSET($K$3,4,-6,1,5),"<>0")+(COUNTIF(OFFSET($K$3,8,-6,1,5),"<>0")+(COUNTIF(OFFSET($K$3,12,-6,1,5),"<>0")+(COUNTIF(OFFSET($K$3,16,-6,1,5),"<>0")))))),OFFSET($K$3,0,-2)-(OFFSET($A$1,2,COLUMN(B7)-1))<6)

The 2nd argument in the AND() is: OFFSET($K$3,0,-2)-(OFFSET($A$1,2,COLUMN(B7)-1))<6. When I test this, it seems to work and returns the correct year, no matter which Quarter the formula references.


Hoping someone can assist. Thank you!

-bbkdude

[sandy666]

First check arguments of the AND() function: =AND(FALSE,{#VALUE!}) so in effect =#VALUE!
btw. Use the AND function, to determine if all conditions in a test are TRUE. If not, CF will show nothing because of FALSE

[bbkdude]

Thank you for the quick reply!

I'm sort of confused by what you are explaining though.

The 2 parts of the AND() CF work individually and format appropriately. But when they are put together, they don't work.

More confusing, is when you put the AND() function into cells on the sheet and you can see that it is returning TRUE for the correct cells that meet both conditions.

On the new attachment, I placed the AND() formula to the right to show that it returns TRUE for cells that are both above the 5yr quarterly average AND within the last 5 years. (Note that C7 is above the 5 yr quarterly average but not within the last 5 yrs, hence FALSE)

Shouldn't the CF be formatting those 3 true cells since they are TRUE?

[sandy666]

I checked results of the

Please Login or Register  to view this content.

entered with Control+Shift+Enter because this is default mode in CF (array formula).
I didn't check construction of your formula but results only because this is not a subject of the thread title. It's your job.
=AND(logical1,logical2)
If you enter your formula with CSE and highlight logical1 nad press F9 you will see value: TRUE or FALSE or error, the same with logical2: TRUE or FALSE or, in this case, #VALUE!
So like I said you need to check correctness of arguments of the AND function.

Please Login or Register  to view this content.

= FALSE (or TRUE)
and

Please Login or Register  to view this content.

= FALSE all the time
both entered with CSE.
so AND(FALSE,FALSE) or AND(TRUE,FALSE) will be FALSE and CF will show nothing

[bbkdude]

Ah, I never knew CF used CSE. Now I do

I looked at the calculation steps for the AND() CSE in a normal cell

Please Login or Register  to view this content.

The (OFFSET($A$1,2,COLUMN(B7)-1)) is what is throwing the #VALUE!, but only when used with the AND(), not when entering as CSE by itself.

In attached, logical1 and logical2 for 2014, 2015, and 2016 both returned true.

Is there any way to accomplish what I am looking for? If I should be looking for help in a different part of this forum, please kindly direct me to that place.

I have no idea where to start with the CSE failing when combined vs the two logicals for AND() both working correctly separately.

[sandy666]

Does the final result look like this?
off.jpg

if not could you set correct result manually on the table with eg. yellow?

[bbkdude]

EDIT: I spoke too soon, 2013 should not be highlighting, only 2014-2018 for this data set.

That is exactly how it should look! All those are above 554,150 and within 5 yrs.

And then, when a year is added/subtraccted, it changes the quarterly average and shifts the CF to apply to the newly created "5 year" set.

[sandy666]

Could you attach example with next years added?

[JeteMc]

If it should be 2014 - 2018 then perhaps the following will be acceptable.
First populate the previously blank first row, beginning with column B and copied over to column I, with the formula:

Please Login or Register  to view this content.

Font color for those cells is set to white.
Second populate the cell XFD1 with the formula:

Please Login or Register  to view this content.

Third use the following formula as a conditional formatting rule for the ranges $B$7:$I$7,$B$11:$I$11,$B$15:$I$15 and $B$19:$I$19 : =AND(B7>$XFD$1,B$1)
Let us know if you have any questions.

[sandy666]

You mean like this?
off.jpg

will you attach example with extended years?

[bbkdude]

That's exactly it, sandy666.

Attached is a test assuming we were in 2020, 2 years added.

I highlighted the new cells that are above the quarterly average (up slightly vs 2018 data).

[sandy666]

Ok,
note 1: this is a monster formula (ca.over 300 characters ) but shorter than yours
note 2: it works with full range selected. You don't need select ranges partially, just B4:K19
note 3: formula will find last year in row 3 itself
note 4: formula will not find cell with "Key Messages" (here: $M$3) itself. It should be changed manually every time you will change (add) a year. E.g. from previous $K$3 to actual $M$3.
note 5: don't use any number in row 3 after last year

• a) if I do it (note 4 to find), formula will be much much longer
• b) I'm too lazy
• c) Sorry for that.

Please Login or Register  to view this content.

Hope it will work for you.
repsolv.gif

[daddylonglegs]

......The (OFFSET($A$1,2,COLUMN(B7)-1)) is what is throwing the #VALUE!......

Sometimes you get problems with COLUMN or ROW functions because they return arrays rather than values, e.g. COLUMN(B7) in the above returns the array {2} rather than the value 2. In some circumstances that isn't a problem....but it seems to cause your problem here. Try using a function like SUM or MAX around the COLUMN function. When I used this version the conditional formatting worked

(OFFSET($A$1,2,SUM(COLUMN(B7))-1))

[protonLeah]

Dynamic Named Ranges:
CurrentYear: =MATCH(YEAR(NOW()),Sheet1!$3:$3,0)
Quarter1: =INDEX(Sheet1!$7:$7,1,CurrentYear-4):INDEX(Sheet1!$7:$7,1,CurrentYear)
Quarter2: =INDEX(Sheet1!$11:$11,1,CurrentYear-4):INDEX(Sheet1!$11:$11,1,CurrentYear)
Quarter3: =INDEX(Sheet1!$15:$15,1,CurrentYear-4):INDEX(Sheet1!$15:$15,1,CurrentYear)
Quarter4: =INDEX(Sheet1!$19:$19,1,CurrentYear-4):INDEX(Sheet1!$19:$19,1,CurrentYear)

Please Login or Register  to view this content.

[bbkdude]

I know it's been a while, but I wanted to follow up and share my results.

Ok,
note 1: this is a monster formula (ca.over 300 characters ) but shorter than yours
note 2: it works with full range selected. You don't need select ranges partially, just B4:K19
note 3: formula will find last year in row 3 itself
note 4: formula will not find cell with "Key Messages" (here: $M$3) itself. It should be changed manually every time you will change (add) a year. E.g. from previous $K$3 to actual $M$3.
note 5: don't use any number in row 3 after last year

• a) if I do it (note 4 to find), formula will be much much longer
• b) I'm too lazy
• c) Sorry for that.

Please Login or Register  to view this content.

Hope it will work for you.
Attachment 512162

This did work, but was a little much for me. I truly appreciate all the help!

I actually ended up splitting the 2 AND functions into their own CF, which would make the cells more than 5 years ago return to normal No Fill status.

Sometimes you get problems with COLUMN or ROW functions because they return arrays rather than values, e.g. COLUMN(B7) in the above returns the array {2} rather than the value 2. In some circumstances that isn't a problem....but it seems to cause your problem here. Try using a function like SUM or MAX around the COLUMN function. When I used this version the conditional formatting worked

(OFFSET($A$1,2,SUM(COLUMN(B7))-1))

I loved this change. This seems to have done the trick and I only needed 1 CF formula!