Conditional formatting with formula

[Ann Scharpf]

I am clearly not understanding how to use the formula in
conditional formatting. Here is what I am trying to do:

Cell B10 copies the value from Sheet2!Z100. If Sheet2!
Z100 is blank, then B10=Average(B7:B9). I am trying to
highlight B10 if is is using an average rather than a
real value.

My actual formula in the cell is:
=IF('Actual TS Hours'!Z20>0,'Actual TS Hours'!Z20,AVERAGE
(B4848))

I went into conditional formatting, selected "formula is"
and entered:

=AVERAGE(B4848)

What's happening is that ALL the cells are highlighted,
whether or not they are equal to the average of the
previous three cells. I tried to really force a false
condition and changed the conditional formula to:

=SUM(B4848)

The cells STILL stayed highlighted.

I have tried looking in help and can't find any guidance
on this. Can anyone enlighten me?

Also, is there a way to SEARCH on this newsgroup? I
could not find a way, so I just paged my way through lots
of pages to see if this question had already been
addressed.

Thanks for any help you can give me.

Ann

 

[RagDyer]

Use these parameters:
"Cell Value is"
"Equal to"
"=Average(B7:B9)"
And then set your formats.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

I am clearly not understanding how to use the formula in
conditional formatting. Here is what I am trying to do:

Cell B10 copies the value from Sheet2!Z100. If Sheet2!
Z100 is blank, then B10=Average(B7:B9). I am trying to
highlight B10 if is is using an average rather than a
real value.

My actual formula in the cell is:
=IF('Actual TS Hours'!Z20>0,'Actual TS Hours'!Z20,AVERAGE
(B4848))

I went into conditional formatting, selected "formula is"
and entered:

=AVERAGE(B4848)

What's happening is that ALL the cells are highlighted,
whether or not they are equal to the average of the
previous three cells. I tried to really force a false
condition and changed the conditional formula to:

=SUM(B4848)

The cells STILL stayed highlighted.

I have tried looking in help and can't find any guidance
on this. Can anyone enlighten me?

Also, is there a way to SEARCH on this newsgroup? I
could not find a way, so I just paged my way through lots
of pages to see if this question had already been
addressed.

Thanks for any help you can give me.

Ann

 

[Biff]

Hi Ann,

Just to add a little insight. Whenever you want to apply
conditional formatting and you need to use a formula, you
can use almost any formula as long as it evaluates to
either TRUE or FALSE. With your example you don't need to
use a formula but you could express it as a formula like

=B10=AVERAGE(B4848)

This would evaluate to TRUE and apply the format. You
cannot use arrays or unions in CF formulas.

As for searching the archives for help, I would highly
recommend this add-in from Ron deBruin. It's a free
download and makes searching the archives via Google very
easy and fast. Take a look:

http://www.rondebruin.nl/Google.htm

Biff

 

[Ann Scharpf]

Thanks to both RD and Biff for their replies! I tried
the "cell value is" method that RD outlined and it worked
like a charm. And I looked at the Google add-in. I will
download it to both my home & work systems!

But, if I can do this with the "cell value is" - why do I
need the formula option? Still don't understand how the
formula works!

Ann

 

[Biff]

Hi Ann,

There are many situations where you want to use CF and the
only way you can define or express the condition is
through a formula.

=B10=AVERAGE(B4848)

Ok, think of it in these terms. Does the value in B10
equal the average of cells B4848. If the answer is YES
then the conditional format is applied. If the answer is
NO then the conditional format is not applied.

CF is a very useful tool and has alot of flexibility due
to the Formula Is option.

With the Google search add-in, you will find that it is
exponentially better than using XL help.

Biff

 

[RagDyer]

Ann,

Try this as an example of the different parameter choices:
A1 contains this formula:

=IF(B1<>"",B1*D1,"")
And since each row designates a day, you drag copy this down ColumnA for
30days to prepare for the month.

You want Column A to alert you when it doesn't contain the value 100.
You would like the cells to be colored yellow and the font to display in red
when *anything* other then the value 100 is displayed.

However, since each row designates a day, and there are as yet unfilled
days, you DON'T want the EMPTY, blank cells (which don't contain 100, just
the formula), to display the warning format.

You would use this set-up:
"Formula Is"
=AND(A1<>100,A1<>"")

If you used:
"Cell Value Is"
"Equal To"
=AND(A1<>100,A1<>"")

It wouldn't work !
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Thanks to both RD and Biff for their replies! I tried
the "cell value is" method that RD outlined and it worked
like a charm. And I looked at the Google add-in. I will
download it to both my home & work systems!

But, if I can do this with the "cell value is" - why do I
need the formula option? Still don't understand how the
formula works!

Ann