Aug 15 2019 12:54 AM
Hi, I have 20+ cells/columns per row with different values and I've added one column giving the average value per row.
Thereafter I've used conditional formatting giving the individual cells red or green colour if it is below or above the average number per row. It is easy to arrange this row by row, but having hundreds of rows I want to copy the formula for the conditional formating with colours to all rows.
E.g. B2 to X2 gives an average of 130 which I show in cell Y. If C2, D2, E2... is above 130 = green. Below 130 = red. etc
Now, B3 to X3 gives an average of e.g. 95... B4 to X4, average e.g. 45...and so on.
When just copying the formatting for row no 2, it relates to the average on row 2.
I want this to relate per row 2, 3, 4...etc with their individual average number per row, without doing this manually row by row, but copying the rule to be valid for all my hundreds of rows, but relating to the individual row-average.
If it was just one number to relate to, it's of course easy, but I need it to follow row by row.
Anyone?
Aug 15 2019 01:37 AM
SolutionYou may apply a rule with the formula like
=B2>$Y2
to entire range, let say $B$2:$X$1000
One rule per color
Aug 15 2019 05:20 AM - edited Aug 15 2019 05:21 AM
@Sergei Baklan Great, thanks Sergei! :):)
Aug 15 2019 05:27 AM
@Anders190210 , you are welcome
Aug 15 2019 01:37 AM
SolutionYou may apply a rule with the formula like
=B2>$Y2
to entire range, let say $B$2:$X$1000
One rule per color