Conditional formatting using formulas

Hi guys

sorry to bother you, but I have been stuck on this particular formula for the last week, and I don’t seem to be progressing any further, and I am hoping you can help


basically there are 6 columns, within these 6 columns there is two kinds of data. The data will either be a number or –. (-) is meant to show n/a, but for reporting purposes I need it to remain (-)

what I am trying to achieve with the formula ,

1)formula searches the multiple columns, and whatever the last number is within those 6 column highlight that particular cell but only highlight the cell if its higher than the average

so if it take row 7, although E7 is higher than average 11, this shouldn’t be highlighted. However h7 should as it’s the last stage and its over the average

if I take row 13, g13 currently has a 12 in it, this is higher than the average of 8, once again this shouldn’t be highlighted as there is another stage, h12 should be highlighted red as it’s the last stage and its higher than the average of 21

row 13, 14 & 15 , column h should be highlighted red as it over 21 day average


Hope this make sense

This is perhaps more complicated than what first meets the eye.

If I read this correctly then my approach would be to create 2 dynamic named ranges

1/. Name:= "DataRow"
Refers to:=

Please Login or Register  to view this content.

2/. Name:= "Averages"
Refers to:=

Please Login or Register  to view this content.

Then this formula for the C/F

Please Login or Register  to view this content.

Applies to:=

Please Login or Register  to view this content.

Hope this helps

Try this:

Select range C4:H18 and apply this formula in conditional formatting

=AND(COUNT(C4:$H4)=1,C4>C$23,ISNUMBER(C4))

see attached

I'd gladly explain, but as usual it is far easier to go with daddylonglegs

Where would the average figures be, will they move too depending on the amount of data?

Apart from that the formula should work "as is", you just have to change the "Applies to" range to C4:H6000 - blank cells won't be formatted so that shouldn't be a problem

The formula just checks three things

=C4>C$23 - the cell value is greater than the average

=ISNUMBER(C4) - the cell value is a number (because "-" is deemed to be greater than any number)

=COUNT(C4:$H4)=1 - there's only 1 number in the range from the current cell all the way to the end of the row - given the previous condition that means that the cell value is the last number in the row

If the average figures will always be at the bottom, i.e. last number in the row then you can use this version

=AND(COUNT(C4:$H4)=1,C4>LOOKUP(99^99,C:C),ISNUMBER(C4))

the LOOKUP part just returns the last number in the column (i.e. the average figure)

Yes, that's almost right, but you have an extra comma that will mess up the results, so you need to remove that - should be

=AND(COUNT(M2:$R2)=1,M2>M$2891,ISNUMBER(M2))

As per my last post, rather than having to identify the average row you could use LOOKUP to look for the last number in the column, so that would make it

=AND(COUNT(M2:$R2)=1,M2>LOOKUP(99^99,M:M),ISNUMBER(M2))

Note that I'm used to selecting the whole range first - that was probably the easiest way in Excel 2003 - but now it's possible to edit the "Applies to" range so I would probably just select the first few rows (must begin at M2) and then edit the "Applies to" range within CF to M2:R2890 or whatever

Hi Daddylonglegs


I tried to apply the formula =AND(COUNT(C4:$H4)=1,C4>C$23,ISNUMBER(C4)) and i have changed all the data fields so that it applies to spreadsheet, however it doesn’t seem to be working correctly

if i upload my data is it possible if you could apply the formula to the spreadsheet, or tell me what the problem is ?


Regards

Masond3

If you upload the sheet I'll take a look.....

Hi

Please find attached spreadsheet which I am trying to apply this to

The columns which it should apply to are “R– V” from row 2 down to 2862
and the average row sits on row 2863 underneath the columns

hope this helps

(reason why spreadsheet not attaching is because it over file size limit) i have to zip it up, so i am hoping this should of worked now )

Seemed to work OK for me. I applied the formula first to a single row, so I selected just R2:V2 and used this formula in conditional formatting

=AND(COUNT(R2:$V2)=1,R2>R$2863,ISNUMBER(R2))

Then I changed the "Applies to" range from =$R$2:$V$2 to =$R$2:$V$2862 and then I got formatting as expected, see attached

Hi

it doesn’t seem to work for me
i am assuming the formula should be =AND(COUNT($R$2:$V$2862)=1,R2>R$2863,ISNUMBER(R2))

I have done conditional formatting , highlighted row R2 down to row V2862

then go to conditional formatting tab, manage rules

show formatting rules for : this table

and still nothing is highlighted

Thank you for sending back your version, i just cnt grasp where i am going wrong


******** update **********

looking at your example , your formula is =AND(COUNT(R2:$V2)=1,R2>R$2863,ISNUMBER(R2))

and i put =AND(COUNT($R$2:$V$2862)=1,R2>R$2863,ISNUMBER(R2))

i take it, that the part after the count, is the reason why its not working ?

Yes, it needs to be the version I suggested - the COUNT part only looks at a single row. The formula doesn't need to be adjusted based on the number of rows - you just need to apply that same formula to a larger range. Excel will automatically adjust so that it looks at each individual row within the range you specify