flag or highlight when number entered exceeds a set value in relation to previous cell

[tchelen]

Okay, don't know if that Title will do it. However hopefully someone can help me with the following. I think it is conditional formatting that I want to use, but it doesn't seem to be quite right.
I have a row of numbers and I want to have the system notify me or colour the cell or in some way indicate when the number entered is below the previous cell's value by say 1,000,000. or perhaps a certain %, say 25%.
It is a double check on our data base numbers to ensure there are no system errors or large fluctuations that are not expected.
With conditional formatting I cannot see how it will allow me to compare the one cell with the previous and enter a value (1,000,000) or a % reduction.
Any suggestions? Please and Thank you in advance.

 

[SydneyGeek]

Hi, and welcome to the Board!

Let's say that you have data in columns A and B, with a reference value in D1.
Select B2 to the end of the data and start conditional formatting.
In 2003 and earlier change Value Is... to Formula Is...
In 2007 select New Rule and choose Formula (last item on the pick list).
In the formula bar, put the formula
=ABS(B2-A2)>=$D$1
SElect the background fill or pattern, click OK to get out of the dialog.

Denis

 

[tchelen]

Thanks Denis: I seem to have managed to apply it to one cell but not the whole column. Here is the formula I put in to adapt it to the column, but it still looks as if it is only applying to D2
=ABS(C2:C48-D2:D48)>=$B$2
(My columns are a little different than your examples. But basically what I am trying to do is if D is less than C by the amount or percentage in B than highlight red.)
How can I get it to apply to the whole column, with each cell referring to the cell to it's left. Is there a way to copy and paste a conditional format?
Or does my formula need adjusting?
Oh, I have the 2007 version.
Thanks for your reply. Helen.

 

[SydneyGeek]

Hi Helen, close...

Select all cells that you want to format in column D, then start the conditional formatting
Type the formula as though you were just interested in row 2.
ie:
=ABS(C2-D2)>=$B$2
The conditional format will apply to all selected cells.

Denis

 

[tchelen]

Wonderful! Thanks Denis, that worked great. You did say to select the range in your first message, but I missed that.
It looks like it all copies with Format Painter as well for when I add new columns.
Thanks again, Helen.

Hi Helen, close...

Select all cells that you want to format in column D, then start the conditional formatting
Type the formula as though you were just interested in row 2.
ie:
=ABS(C2-D2)>=$B$2
The conditional format will apply to all selected cells.

Denis