SOLVED
Home

Conditional formatting to highlight cell with higher than the average of the row.

%3CLINGO-SUB%20id%3D%22lingo-sub-773516%22%20slang%3D%22en-US%22%3EConditional%20formatting%20to%20highlight%20cell%20with%20higher%20than%20the%20average%20of%20the%20row.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-773516%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%2052%20weeks%20of%20shipping%20volume%20numbers%20sorted%20in%2052%20columns.%20The%2053rd%20column%20is%20the%20average%20of%20the%2052%20weeks.%20Currently%2C%20I%20able%20to%20set%20a%20conditional%20format%20to%20high%20lite%20all%20cells%20in%20a%20particular%20row%20that%20are%20greater%20than%20the%20average%20in%20column%2053.%20Can%20anyone%20tell%20me%20how%20to%20copy%20this%20format%20to%20the%20rest%20of%20the%20rows%20since%20they%20each%20have%20different%20averages.%20I%20dont%20want%20to%20set%20the%20same%20conditional%20format%20on%20over%201000%20different%20shipping%20lanes.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEdit%3A%20To%20simplify%2C%20each%20row%20is%20a%20different%20shipping%20lane.%20I%20want%20to%20highlight%20the%20weeks%20that%20exceed%20the%20lane's%20AVG%20cell%20as%20shown%20below.%20Is%20there%20a%20way%20to%20apply%20this%20format%20to%20the%20rest%20of%20the%20data%20without%20doing%20each%20individual%20shipping%20lane%3F%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EVolume%3A%20Week%2045%3C%2FTD%3E%3CTD%3EVolume%3A%20Week%2046%3C%2FTD%3E%3CTD%3EVolume%3A%20Week%2047%3C%2FTD%3E%3CTD%3EVolume%3A%20Week%2048%3C%2FTD%3E%3CTD%3EVolume%3A%20Week%2049%3C%2FTD%3E%3CTD%3EVolume%3A%20Week%2050%3C%2FTD%3E%3CTD%3EVolume%3A%20Week%2051%3C%2FTD%3E%3CTD%3EVolume%3A%20Week%2052%3C%2FTD%3E%3CTD%3EAVG%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E%3CSTRONG%3E6%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3E11%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3E12%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3E27%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3E21%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3E22%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3E10%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E%3CSTRONG%3E4%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3E12%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3E7%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3E6%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CSTRONG%3E15%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3E21%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3E23%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E10%3C%2FTD%3E%3CTD%3E%3CSTRONG%3E24%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3E30%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E12%3C%2FTD%3E%3CTD%3E12%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-773516%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EConditional%20Formatting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-773594%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20to%20highlight%20cell%20with%20higher%20than%20the%20average%20of%20the%20row.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-773594%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F381691%22%20target%3D%22_blank%22%3E%40ESKARDA%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EUse%20the%20rule%20with%20formula%20as%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20617px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F124489iECD6CA2ED01FA11B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eand%20apply%20it%20to%20your%20entire%20range.%20Only%20one%20rule.%20Just%20be%20careful%20with%20absolute%20and%20relative%20references.%3C%2FP%3E%0A%3CP%3EPlease%20check%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-775422%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20to%20highlight%20cell%20with%20higher%20than%20the%20average%20of%20the%20row.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-775422%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BOk%20so%20now%20I%20need%20to%20average%20those%20cells%20that%20are%20now%20highlighted.%20What%20is%20the%20best%20way%20to%20do%20that%3F%20I%20tried%20using%20the%20AVERAGIF%20formula%20but%20it%20is%20returning%20an%20error.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-775490%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20to%20highlight%20cell%20with%20higher%20than%20the%20average%20of%20the%20row.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-775490%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F381691%22%20target%3D%22_blank%22%3E%40ESKARDA%3C%2FA%3E%26nbsp%3B%2C%20I%20added%20formula%20into%20the%20next%20column%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-csharp%22%3E%3CCODE%3E%3DAVERAGEIF(%24A2%3A%24AZ2%2C%22%26gt%3B%3D%22%26amp%3B%24BA2)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EPlease%20check%20attached%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-775562%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20to%20highlight%20cell%20with%20higher%20than%20the%20average%20of%20the%20row.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-775562%22%20slang%3D%22en-US%22%3E%3CP%3EYour%20are%20my%20new%20favorite%20person!%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
ESKARDA
New Contributor

I have 52 weeks of shipping volume numbers sorted in 52 columns. The 53rd column is the average of the 52 weeks. Currently, I able to set a conditional format to high lite all cells in a particular row that are greater than the average in column 53. Can anyone tell me how to copy this format to the rest of the rows since they each have different averages. I dont want to set the same conditional format on over 1000 different shipping lanes. 

 

Edit: To simplify, each row is a different shipping lane. I want to highlight the weeks that exceed the lane's AVG cell as shown below. Is there a way to apply this format to the rest of the data without doing each individual shipping lane?

Volume: Week 45Volume: Week 46Volume: Week 47Volume: Week 48Volume: Week 49Volume: Week 50Volume: Week 51Volume: Week 52AVG
361112272122104
0041276001
15212310243021212
4 Replies
Solution

@ESKARDA 

Use the rule with formula as

image.png

and apply it to your entire range. Only one rule. Just be careful with absolute and relative references.

Please check attached.

@Sergei Baklan Ok so now I need to average those cells that are now highlighted. What is the best way to do that? I tried using the AVERAGIF formula but it is returning an error.

@ESKARDA , I added formula into the next column

=AVERAGEIF($A2:$AZ2,">="&$BA2)

Please check attached