jaykoski99x
Thu May 15 11:59:03 CDT 2008
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(B2>AVERAGE(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(C2>AVERAGE(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(D2>AVERAGE(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
> >