Hello again everyone,
I try so hard to not do this because it proves what an Excel-dummy I really am, but I am having difficulties - AGAIN. I have a page of figures (range A2:AP479) in which each column displays the number of weeks between uses of the items in Row1 (eg. A1=A2:A479 etc.) I am trying to highlight the greatest number in a run. The best way to describe what I need is to set out an example:
4
5
6
0
1
2
3
0
0
1
2
3
4
0
1
2
I want to highlight the 6, the 3, the 0 (where there is more than one zero, as that is the highest number in that run), the 4 and the 2.
Using A2 as a base I used the formula =A3<1 and set the highlight colour. Over 70% of the page it has worked well, but for the other 30% it is also highlighting the zeros before the 1s. I can't figure out its way of thinking; it just doesn't appear to be consistent. For instance, it might highlight the zero between the 6 and the 1, but not the zero between the 4 and the 1, yet nicely highlights only one of the zeros of the double (or two of a triple etc.)
I thought perhaps it was a problem with the <1 but with <0 or even =0 it does not work at all.
Any help, or even just explanation of its way of thinking, would be greatly appreciated. In short, I want to highlight any cell when the cell below it contains a zero.
Many thanks in advance.
I try so hard to not do this because it proves what an Excel-dummy I really am, but I am having difficulties - AGAIN. I have a page of figures (range A2:AP479) in which each column displays the number of weeks between uses of the items in Row1 (eg. A1=A2:A479 etc.) I am trying to highlight the greatest number in a run. The best way to describe what I need is to set out an example:
4
5
6
0
1
2
3
0
0
1
2
3
4
0
1
2
I want to highlight the 6, the 3, the 0 (where there is more than one zero, as that is the highest number in that run), the 4 and the 2.
Using A2 as a base I used the formula =A3<1 and set the highlight colour. Over 70% of the page it has worked well, but for the other 30% it is also highlighting the zeros before the 1s. I can't figure out its way of thinking; it just doesn't appear to be consistent. For instance, it might highlight the zero between the 6 and the 1, but not the zero between the 4 and the 1, yet nicely highlights only one of the zeros of the double (or two of a triple etc.)
I thought perhaps it was a problem with the <1 but with <0 or even =0 it does not work at all.
Any help, or even just explanation of its way of thinking, would be greatly appreciated. In short, I want to highlight any cell when the cell below it contains a zero.
Many thanks in advance.
