Conditional Formatting utilizing 1 StDev Above Formula

Hi my name is Jordan,

I need help in creating a formula within the conditional formatting menu that will highlight the cells in yellow that are 1 standard deviation above the other cells in the range. For example, let's say I have January through August, 8 months, going across in columns A:H. Starting in row two under these months going from Jan to Aug, I have these percentages: 5%, 11%, 51%, 35%, 7%, 19%, 70%, 18%.

51% and the 70% should be the cells that get highlighted. How should the formula be written to perform this in the menu --- Conditional Formatting > New Formatting Rule > Use a formula to determine which cells to format

My intention is to be able to create this formula and be able to copy this conditional formatting to many rows individually below.

I've attached my sample file to this thread. Any help would be greatly appreciated.

Thanks,

Awesome thank you that worked 63falcondude! Now, if I have 10 rows of similar percentages below this row B, how do I quickly copy down the conditional formatting so that this same formula will have its affect individually on rows $B5:$I5, $B6:$I6, $B7:$I7, etc.?

When I use the format painter and drag down or copy $B4:$I4 and paste formatting to the next 10 rows below, it does not apply the CF to the rows individually, but rather as a whole on the entire 10 rows.

That's interesting... When I highlight B4:I4 (which was formatted using the formula from post #2), select the format painter, then select B5:I14, the CF works row by row as expected.

So I've added in 9 more rows below row 4. I then selected b4:i4 that have your StDev formula in the CF settings. I used the format painter and drug down through I13. It seems to be working properly but when I select, say cells b13:i13 and look at the formula again within the conditional formatting settings the formula references row 5. Why would this be? Why would it not reference row 13?

See attached updated file

Okay good. The Conditional Formatting is working properly. The only issue here is understanding how it is working.

Notice that the "Applies to" range is $B$5:$I$13. The CF formula is applied to that range starting in the upper left most cell (B5).

So in B5, the formula is =B5>=STDEV($B5:$I5)+AVERAGE($B5:$I5)
As the formula is copied across and down the range, it changes to the following:

C5 =C5>=STDEV($B5:$I5)+AVERAGE($B5:$I5)
D5 =D5>=STDEV($B5:$I5)+AVERAGE($B5:$I5)
...
B6 =B6>=STDEV($B6:$I6)+AVERAGE($B6:$I6)
...
B7 =B7>=STDEV($B7:$I7)+AVERAGE($B7:$I7)

But the rule only shows the formula used in the top left most cell of the "Applies to" range.