conditional formatting: greater or less than 5% of average

Enter your % diff in, say, cell H1.

Then select cells A1:G1, and apply CF using the Formula option, with this formula (to identify above the average)

=A1>(AVERAGE($A1:$G1)*(1+$H1))

and choose your format. Then do that again, and add another level, with this formula (for below average)

=A1<(AVERAGE($A1:$G1)*(1-$H1))

If you don't care about differentiating above and below, then just use

=OR(A1>(AVERAGE($A1:$G1)*(1+$H1)), A1<(AVERAGE($A1:$G1)*(1-$H1)))

Hello Bernie,
thank you so much for your quick answer. Unfortunately it doesn't show me the cells that are off.
formula.jpg
formula1.jpg

Change the $H1 to $H$1 in each of the formulae and put the difference in cell H1, for example, 5%

Two things :

1) Enter 0.01 or 1% not just the number 1. 1 is the same as 100%

2) Use $I$9 if you want that one cell to apply to multiple rows: my formula was written to only act on one row so that if it were copied down the limit would also rely on the value entered into that row.

The D11 should be whatever the activecell is at the time that the formula is written - Excel will update cell references just as if a formula were copied from that cell to the others. So, if you selected D11:G11 by clicking and dragging from D11 to G11, then the formula should use D11. If you clicked G11 and dragged to D11, then the D11 should be G11. The correct address should be shown to the far left of the formula bar, which was not included in your screen capture post.

Hello Bernie,
somehow I understand what you mean, but then still I don't understand...
Is it possible to avoid either D11 or G11 but just have the average of the whole row and from that average have the 5% off formatting?
And does this also mean I can't use the format painter and expect the formula adjust accordingly since it would always refer to D11 (or G11)?
I would have to use the rows D12, D13 etc as well as O11, O13 etc

I've tried to change D11 to another cell where I have written the average across the row but it didnt change anything - probably since there is an average in the formula already

OK - let's look at my first formula, applied to cell A1:


=A1>(AVERAGE($A1:$G1)*(1+$H1)

When copied to B1, the formula becomes

=B1>(AVERAGE($A1:$G1)*(1+$H1)

Nothing about the columns referred to changes except for the A1 to B1.

When the formatting is copied to A2, that formula becomes


=A2>(AVERAGE($A2:$G2)*(1+$H2))

The row changes because the $ are only applied to the column addresses.

Remember, $ before a column or row prevents that reference from being updated when the formula (or formatting, in this case) is copied and pasted.