If you take a look at my example below... We'll say I'm selling widgets. I
need my crew to average 4500 widgets in my two week period. I can use
conditional formatting to make the number in the "Avg" column green if they
sell good, red if they sell bad (or whichever text colors, formats, etc).
I've got that down.

What if instead of formatting my "Avg" column, I put in a column to
designate whether they did "OK" or not? For instance, Ben sold ABOVE the
average I'm looking for, and I want a special character like a green check
mark to populate in my "OK" column. Conversely, Quark sold BELOW the average
I'm looking for, and I want a special character like a red "X" to populate in
my "OK" column.

I'm sure that it involves some of the IF, THEN, AND stuff that I'm really
weak on. Any ideas on this puzzle?

Week 1 Week 2 Avg OK?
Ben 8627 5136 6882
Julian 3578 5648 4613
Kira 1597 5975 3786
Odo 5684 5812 5748
Quark 3248 5211 4230
Miles 4008 9855 6932
Jake 6032 6871 6452
Dax 5352 8988 7170

Well, first, I'm not certain that comparing people's sales to the average is
all that meaningful. Remember that in order to have an average, you must
have a high and a low value somewhere. No matter how strong your sales force
is, unless they all sell the same, someone is always going to be top dog and
someone will always be at the bottom. Unless you're looking for people who
are consistently at the top/bottom of their game.

Anyhow - what if we go with 2 columns for your check/X entries? One for
week 1 and one for week 2? And in each we compare the individual's sales to
the average of the group for that week?

Start by formatting the cells that will have the check/X entries with the
Wingdings 2 font. That allows a capital O to display as an x and a capital P
to display as a check mark. Then all you have to do is set conditional
formatting for those cells to turn text red if they contain "O" and green if
they contain "P".
The formula in those cells might look like this (row 2 used as example)
=IF(B2AVERAGE(B$2:B$9),"P",IF(B2<AVERAGE(B$2:B$9) ,"O",""))
that compares their sales in week 1 to the group's average for week 1, while
=IF(C2AVERAGE(C$2:C$9),"P",IF(C2<AVERAGE(C$2:C$9) ,"O",""))
would compare their sales in week 2 to the group's average for week 2.

Or another approach, using the layout you have, would be to compare their
individual 2-week average to the group's 2 week average:
=IF(D2AVERAGE(D$2:D$9),"P",IF(D2<AVERAGE(D$2:D$9) ,"O",""))


"jaykoski99x" wrote:

If you take a look at my example below... We'll say I'm selling widgets. I
need my crew to average 4500 widgets in my two week period. I can use
conditional formatting to make the number in the "Avg" column green if they
sell good, red if they sell bad (or whichever text colors, formats, etc).
I've got that down.

What if instead of formatting my "Avg" column, I put in a column to
designate whether they did "OK" or not? For instance, Ben sold ABOVE the
average I'm looking for, and I want a special character like a green check
mark to populate in my "OK" column. Conversely, Quark sold BELOW the average
I'm looking for, and I want a special character like a red "X" to populate in
my "OK" column.

I'm sure that it involves some of the IF, THEN, AND stuff that I'm really
weak on. Any ideas on this puzzle?

Week 1 Week 2 Avg OK?
Ben 8627 5136 6882
Julian 3578 5648 4613
Kira 1597 5975 3786
Odo 5684 5812 5748
Quark 3248 5211 4230
Miles 4008 9855 6932
Jake 6032 6871 6452
Dax 5352 8988 7170

That's actually really good! Thanks! My scenario isn't real life of course,
but I just wanted to have some data for an example. Having people from Star
Trek sell my stuff would be a pretty cool job, though... :)

"JLatham" wrote:

Well, first, I'm not certain that comparing people's sales to the average is
all that meaningful. Remember that in order to have an average, you must
have a high and a low value somewhere. No matter how strong your sales force
is, unless they all sell the same, someone is always going to be top dog and
someone will always be at the bottom. Unless you're looking for people who
are consistently at the top/bottom of their game.

Anyhow - what if we go with 2 columns for your check/X entries? One for
week 1 and one for week 2? And in each we compare the individual's sales to
the average of the group for that week?

Start by formatting the cells that will have the check/X entries with the
Wingdings 2 font. That allows a capital O to display as an x and a capital P
to display as a check mark. Then all you have to do is set conditional
formatting for those cells to turn text red if they contain "O" and green if
they contain "P".
The formula in those cells might look like this (row 2 used as example)
=IF(B2AVERAGE(B$2:B$9),"P",IF(B2<AVERAGE(B$2:B$9) ,"O",""))
that compares their sales in week 1 to the group's average for week 1, while
=IF(C2AVERAGE(C$2:C$9),"P",IF(C2<AVERAGE(C$2:C$9) ,"O",""))
would compare their sales in week 2 to the group's average for week 2.

Or another approach, using the layout you have, would be to compare their
individual 2-week average to the group's 2 week average:
=IF(D2AVERAGE(D$2:D$9),"P",IF(D2<AVERAGE(D$2:D$9) ,"O",""))


"jaykoski99x" wrote:

If you take a look at my example below... We'll say I'm selling widgets. I
need my crew to average 4500 widgets in my two week period. I can use
conditional formatting to make the number in the "Avg" column green if they
sell good, red if they sell bad (or whichever text colors, formats, etc).
I've got that down.

What if instead of formatting my "Avg" column, I put in a column to
designate whether they did "OK" or not? For instance, Ben sold ABOVE the
average I'm looking for, and I want a special character like a green check
mark to populate in my "OK" column. Conversely, Quark sold BELOW the average
I'm looking for, and I want a special character like a red "X" to populate in
my "OK" column.

I'm sure that it involves some of the IF, THEN, AND stuff that I'm really
weak on. Any ideas on this puzzle?

Week 1 Week 2 Avg OK?
Ben 8627 5136 6882
Julian 3578 5648 4613
Kira 1597 5975 3786
Odo 5684 5812 5748
Quark 3248 5211 4230
Miles 4008 9855 6932
Jake 6032 6871 6452
Dax 5352 8988 7170