conditional formatting

dq1963

New Member
Joined
Jan 30, 2013
Messages
20
Hi,

How do I do "conditional formatting" to high light the numbers which bigger than average + 2 * stdev in a data group?

Thanks.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Longhand without excel, so no testing, but something like:

=abs(a1-mean($a$1:$a$10))> = abs(stdev($a$1:$a$10))*2
 
Upvote 0
Hi PaddyD, Thanks for your response.

I tried your formula, but didn't get the answer. Could you help me a little more?
I have a group of data like the following:
326811384087425718326308350365353319406317442552437142267650474358454730
366879370508355355334905370286348441322219336154393136394277357988399553
333649348960375930395849367434402701413857426277433760432747466137415953
312525365992328181268164343826369569380899373392396336377097403879371538
314470338382390523374733375390384435401273380457398163416153441370444255
330464311759319292330443361176362840417093209346329265386275360393386416
308416354482319235374420343546333707350656361731330513365170323886387296
502244302705312229286208362361187764352238168765348557261690352421169385
313039327658354128378581344832337639382657391990318230395927372407381769
327128315282297285315547340308315275326413326173291609333172230421320123

<COLGROUP><COL style="WIDTH: 42pt" span=12 width=56><TBODY>
</TBODY>
I would like to high light the numbers (showing different cell color or whatever) which are bigger than mean + 2 * stdev.

Thank you.
 
Upvote 0
Conditional format A1 as formula =A1>AVERAGE($A$1:$L$10)+(2*STDEV($A$1:$L$10)) in your highlight colour.

Copy formatting to other cells in the data range.

 
Upvote 0

Forum statistics

Threads
1,206,971
Messages
6,075,925
Members
446,170
Latest member
zzzz02

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top