using conditional formatting - blank cells

I want to use three colours to highlight a spreadsheet, red for numbers less
than -1, amber for number between -1 and 0, and green for numbers greater
than 0 which I can do no problem. Each cell that I am colour formatting has
the formula (or similar):
=IF(G2="","",IF(M2="","",M2-G2))

However the blank cells are also being coloured in green when I want them to
remain white. Any ideas?

I use a good trick. A change the formula to:

=IF(G2="",0,IF(M2="",0,M2-D2))

Get the result. But a problem. Excel was displaying the 0. Then I uncheck
the "Zero Values" option from Tools - Options - View tab.

Hope this helps you too. Let us know!

"SD" wrote:

> I want to use three colours to highlight a spreadsheet, red for numbers less
> than -1, amber for number between -1 and 0, and green for numbers greater
> than 0 which I can do no problem. Each cell that I am colour formatting has
> the formula (or similar):
> =IF(G2="","",IF(M2="","",M2-G2))
>
> However the blank cells are also being coloured in green when I want them to
> remain white. Any ideas?

One problem, my sheet is to analyse student progress and the sheet works out
the levels students have gone up or down in. Therefore the sheet displays
positive and negative integer values including 0 which I need to see (the 0
meaning no progress made from the last assessment). However I want the cell
to be blank when a student has been absent from an assessment so it does not
give me an incorrect progress level.

I want to use a traffic light system below:

-2 or below = red
-1 and 0 = amber
1 or above = green
any blanks = white

So I don't think your suggestion would work here...

Any other ideas?

I got it. See the link below:

http://exceltips.vitalnews.com/Pages...nd_Zeroes.html

Hope this will solve the problem. Let us know.

"SD" wrote:

> One problem, my sheet is to analyse student progress and the sheet works out
> the levels students have gone up or down in. Therefore the sheet displays
> positive and negative integer values including 0 which I need to see (the 0
> meaning no progress made from the last assessment). However I want the cell
> to be blank when a student has been absent from an assessment so it does not
> give me an incorrect progress level.
>
> I want to use a traffic light system below:
>
> -2 or below = red
> -1 and 0 = amber
> 1 or above = green
> any blanks = white
>
> So I don't think your suggestion would work here...
>
> Any other ideas?

Assume your cells to be formatted are in H2 and below:
1st condition: cell value <= -2, format red
2nd condition: formula: =AND(H2<=-1,NOT(ISBLANK(H2))), format amber
3rd condition: cell value >= 1, format green

Regards,
Stefi


„SD” ezt �*rta:

> One problem, my sheet is to analyse student progress and the sheet works out
> the levels students have gone up or down in. Therefore the sheet displays
> positive and negative integer values including 0 which I need to see (the 0
> meaning no progress made from the last assessment). However I want the cell
> to be blank when a student has been absent from an assessment so it does not
> give me an incorrect progress level.
>
> I want to use a traffic light system below:
>
> -2 or below = red
> -1 and 0 = amber
> 1 or above = green
> any blanks = white
>
> So I don't think your suggestion would work here...
>
> Any other ideas?

Did not manage to do it that way.

I ended up calling the blank cells "z" (say) and then by colouring the cells
green to begin with, I used the 3 conditions to
1.) colour any z's with a white background and white font
2.) highlight in amber -1's and 0's
3.) highlight in red values less than -1.

Messy but it finally worked.

Thanks for the other tips, will come in useful.