How to find value that is bigger or smaller than 2SD?

[Guest]

I am new to excel, my question may be silly for you. But I really don't know
how to do it and hope to get your help.

I need to do statistic analysis of my data table which has lots of columns.
First of all, in each column, I want to find and highlight the value that is
bigger than mean+two times standard derivation, or smaller than mean-2*
standard derivation. Do you have any suggestions about how to do it?

Thanks alot,

ming

 

[Zack Barresse]

Hello,

Maybe something like this ...

Sub HighlightForMePlease()
Dim rngLook As Range, c As Range, firstAddy As String
Dim lngSTDEV1 As Double, lngSTDEV2 As Double
Set rngLook = Sheets("test").Range("A2:F15") '<<== CHANGE THIS TO SUIT
With Application.WorksheetFunction
lngSTDEV1 = .Average(rngLook) + .StDev(rngLook)
lngSTDEV2 = 2 * .StDev(rngLook)
End With
For Each c In rngLook
If IsNumeric(c) Then
If c.Value < lngSTDEV1 And c.Value > lngSTDEV2 Then
c.Interior.ColorIndex = 3
End If
End If
Next
End Sub

 

[Bill Kuunders]

Enter in a cell A1 =AVERAGE(A3:A10)+2*(STDEV(A3:A10)) for example

Go to <format><conditional format> and enter the condition to be cell value
greater than A1
<format......> to select the pattern or colour you would like to see as cell
colour.

enter in cell A2 =AVERAGE(A3:A10)-2*(STDEV(A3:A10))
etc

 

[Guest]

Hi Zack and Aka,

Thanks alot for your suggestions. However, I am not good at programming.
Could you please tell me where should I type the programme you made for me?
Is there any other way of doing it?

I appreciate your help,

ming

 

[Guest]

Hi Bill,

Thanks for your great suggestions. I like it very much. But, I feel like I
have to do the formating twice for each column--greater than and less than.
Since I have alot of columns in one table, and I have a couple of tables, I
am wondering if you have any other suggestions of doing all the columns
simultaneously?

I really appreciate your time and help,

ming

 

[JE McGimpsey]

Take a look at David McRitchie's "Getting Started with Macros":

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Bill Kuunders]

Enter the formula's in cells A1 and A2
extend them accross the page above your columns.
(use the right hand bottom corner of the cell when the mouse pointer changes
to a + click and drag it accross)


Do the formatting for column 1
enter in the conditional formatting window cell value ....... greater than
..........=A$1
note the $ sign only in front of the 1.
use the add>> button to add your second condition
cell value.........less than.......=A$2 give it a different colour
You can now use the format painter.
highlight your area in column 1 click on the format painter highlight the
other columns and
it's done.

format painter is the yellow paint brush icon
Any other problems or questions come back to us.
Have fun
Greetings from New Zealand
Bill K

 

[Guest]

Hi Bill,

This is really cool! Now I am playing with it and enjoying the magic the
little brush can bring to me. It's wonderful! And thanks again for your great
suggestions.

Have a wonderful day,

ming

 

[Bill Kuunders]

That's good.
Thanks for the feedback.

Greetings from New Zealand
Bill K