SOLVED
Home

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

ESKARDA
Occasional 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 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies