1
2 Rep A 1 6 4 5 3 0 5 3
3 Rep B 0 0 0 0 0 0 0 0
4 Rep C 0 4 3 5 2 4 3 3
5 Rep D 0 12 8 4 2 1 4 4
6 Rep E 3 2 11 7 1 1 10 6
7 Rep F 0 9 7 7 1 3 3 9
Coulmn A- Rep Name
Column B- 1/2/08
Column C- 1/16/08
Column D- 1/31/08
Column E- 2/14/08
Column F- 2/29/08
Column G- 3/3/08
Column H- 3/17/08
Column I- 3/31/08
The number in the columns is the number of sign-ups a rep had for that particular date. So, rep A had 1 for the 2nd of January, and 6 for the 16th, and 4 for the 31st. What I want to do is use conditional formatting to color format, and Bold the fields of the person with the highest combined total for the month. For example, if you look above, Rep D had the most sign-ups for the month of January with 20, so I would like B5 5 to be color formatted and bold.
This would change thoughout the month. If Rep A was in the lead for the month, then he would be formatted, but if Rep B passed him then "B" would be formatted, and rep A would go back to normal.
Is this possible?
I'm not sure how to do conditional formatting by evaluating multiple cells on multiple lines but here is a work around that will work. Just create monthly summary for your reps and use those results to compare for the highest value for a particular month in your data area. I have done an example but the html program does not show the conditional formatting results. In cell B2, use this formula for conditional formatting. =$B12=LARGE($B$12 B$17,1). That compares the Jan total for rep A against all of the reps totals for January. Just copy this formatting on all cells B27. (When you copy the formatting, the cell references will update accordinly.) Use =$C12=LARGE($C$12C$17,1) for cells E2:E7 and use =$D12=LARGE($D$12D$17,1) for cells G2:I7.
You can insert the totals columns after each month of data but wasn't sure how you wanted to handle that. Others may have a better solution than this but this will work.
Good Luck
******** ******************** src="from:>*********>sun/puremis/colo/popup.js">*********>Microsoft Excel - Book2___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp :void(0)" target="_blank">(A)boutB12C12D12B13C13D13B14C14D14B15C15D15B16C16D16B17C17D17=
ABCDEFGHI1Rep1/2/20081/16/20081/31/20082/24/20082/29/20083/3/20083/17/20083/31/20082Rep A164530533Rep B000000004Rep C043524335Rep D0128421446Rep E32117111067Rep F097713398910Totals11RepJan TotalsFeb TotalsMar Totals12Rep A:void(0);" target="_blank">11:void(0);" target="_blank">8:void(0);" target="_blank">813Rep B:void(0);" target="_blank">0:void(0);" target="_blank">0:void(0);" target="_blank">014Rep C:void(0);" target="_blank">7:void(0);" target="_blank">7:void(0);" target="_blank">1015Rep D:void(0);" target="_blank">20:void(0);" target="_blank">6:void(0);" target="_blank">916Rep E:void(0);" target="_blank">16:void(0);" target="_blank">8:void(0);" target="_blank">1717Rep F:void(0);" target="_blank">16:void(0);" target="_blank">8:void(0);" target="_blank">15Sheet1
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.mcintoshmc,
Hope this helps.
Sheet1 #160;ABCDEFGHIJKL1Rep Name1/2/20081/16/20081/31/2008202/14/20082/29/200883/3/20083/17/20083/31/2008172RepA1641153805383RepB000000000004RepC0437527433105RepD01282042614496RepE3211167181106177RepF09716718339158#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;9#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;10#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;11#160;#160;#160;#160;=SUM#40;B2#58;D2#41;=$E$1#160;#160;=SUM#40;F2#58;G2#41;=$H$1#160;#160;#160;=SUM#40;I2#58;K2#41;=$L$112#160;#160;#160;#160;=SUM#40;B2#58;D2#41;#60;#62;$E$1#160;#160;=SUM#40;F2#58;G2#41;#60;#62;$H$1#160;#160;#160;=SUM#40;I2#58;K2#41;#60;#62;$L$113#160;=$E2=$E$1#160;=$H2=$H$1#160;=$L2=$L$1#160;14#160;=$E2#60;#62;$E$1#160;=$H2#60;#62;$H$1#160;=$L2#60;#62;$L$1#160;15#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;#160;Spreadsheet FormulasCellFormulaE1=MAX#40;E2#58;E7#41;H1=MAX#40;H2#58;H7#41;L1=MAX#40;L2#58;L7#41;E2=SUM#40;B2#58;D2#41;H2=SUM#40;F2#58;G2#41;L2=SUM#40;I2#58;K2#41;E3=SUM#40;B3#58;D3#41;H3=SUM#40;F3#58;G3#41;L3=SUM#40;I3#58;K3#41;E4=SUM#40;B4#58;D4#41;H4=SUM#40;F4#58;G4#41;L4=SUM#40;I4#58;K4#41;E5=SUM#40;B5#58;D5#41;H5=SUM#40;F5#58;G5#41;L5=SUM#40;I5#58;K5#41;E6=SUM#40;B6#58;D6#41;H6=SUM#40;F6#58;G6#41;L6=SUM#40;I6#58;K6#41;E7=SUM#40;B7#58;D7#41;H7=SUM#40;F7#58;G7#41;L7=SUM#40;I7#58;K7#41; Excel tables to the web #62;#62; Excel Jeanie HTML 4
I added columns between the months.
Formula in cell E1:
=MAX(E2:E7)
Formula in cell E2, copied down:
=SUM(B22)
Conditional Formatting for highlighed Range("E2:E7"):
Condition 1: Formula Is: =SUM(B22)=$E$1 BOLD
Condition 2: Formula Is: =SUM(B22)$E$1
Conditional Formatting for highlighed Range("B27"):
Condition 1: Formula Is: =$E2=$E$1 BOLD
Condition 2: Formula Is: =SUM(B22)$E$1After applying conditional formatting to the sheet, hide the additional columns E, H, and L.Have a great day,
Stan
thanks, you guys are great!!
Hi mcintoshmc
It's also possible to use just one formula.
Disadvantages: not a friendly formula, not as efficient
Advantages: just 1 formula for the whole table, no auxilliary data, easier to adjust
With gnrboyd's layout (that seems to be also your's), select B2:I7 and use for conditional formatting formula:
=MAX(MMULT(IF(MONTH($B$1I$1)=MONTH(B$1),$B$2I$7,0),--TRANSPOSE(COLUMN($B$1I$1)>0)))=INDEX(MMULT(IF(MONTH($B$1I$1)=MONTH(B$1),$B$2I$7,0),--TRANSPOSE(COLUMN($B$1I$1)>0)),ROWS(B$2:B2))
pgc01,
Amazing.....
What a great site for learning.Have a great day,
Stan
gnr boyd,
I used your formula, but they are all formatted when there are zero's. As soon as someone takes the lead with 1, then only that person will be formatted. So, basically all the future dates are highlighted. How can I fix this?
mcintoshmc... Just add an if statement to your conditional formatting.
Instead of =$B12=LARGE($B$12B$17,1) use =IF($B12>0,$B12=LARGE($B$12:$B$17,1),"")
This will only qualify those cells with a value greater than zero. (Sorry for delay...have not been on the site for a while.)
Just curious, but why not arrange your data like this
Code:
Rep Date Number
A 1/2/08 1
B 1/1/08 2
'etc
A 1/16/08 1
C 1/16/08 3
'etc
Now a Pivot Table will give you all the info you need, plus rank the data by Month, Week, Quarter, whatever with NO formulae.
lenze
Thank You... |
Post at 2011-5-24 22:03
| This Author's Posts Only
