I don't know where to start with this.

But what I'm going to do for a while is deal with 25cm of snow that dumped
in the past 4 hours...............and more coming all night.

I vill be baawwwk


Gord

On Wed, 17 Dec 2008 12:58:01 -0800, DOUG ECKERT
wrote:

Gord: I do not know how to put CF in a formula, beyond using the CF tool.

What this person wants to do is to look at all of the compliant cells in a
row. If ALL cells are compliant, the overall grade is a GREEN stoplight - as
in, "Good to Go". If ANY cells are rated marginal - with scores in the
marginal or "YELLOW" range - the summary field would show a YELLOW stoplight.
If rated below the YELLOW range, it would show a RED stoplight. Any RED
cells would generate a RED stoplight in the summary field. (Compliance will
be noted as above a certain threshold equals YES, or in some cases YES equals
Yes, based on checklist questions. The individual cells contain numbers to
be compared to a certain goal, which is reflected in another cell, at least
in most cases). In short, either ALL Comply, SOME Comply (with no RED
scores), or NONE comply (if ANY RED scores). So far, I have tried the IF,
AND and LOOKUP functions, to great frustration. Cell colors are based on
Conditional Formatting, but the formula could reference the cell containing
the compliance goal, at least for generating the GREEN stoplight. Adding the
YELLOW and RED criteria is a great deal more complicated.

"IF" worked, until I ran out of nested functions to use.

"Gord Dibben" wrote:

How did the cells get to be green or yellow?

If by Conditional Formatting, use the CF criteria in your formula.

If manually colored you will need to add a User Defined Function to
ascertain the color.

See Chip Pearson's site for some code.

http://www.cpearson.com/excel/colors.aspx


Gord Dibben MS Excel MVP

On Wed, 17 Dec 2008 11:24:09 -0800, DOUG ECKERT
wrote:

'Anybody?

DOUG

"DOUG ECKERT" wrote:

I have a co-worker who wants to insert a formula to say "If all of the cells
in a range are green, then this cell is green. If some cells are yellow,
then this cell is yellow. If any cells are red, this cell is red".

??

DOUG