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

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

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


--
Regards,
Zack Barresse, aka firefytr

"learner" wrote in message
...
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

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
--
Greetings from New Zealand
Bill K

"learner" wrote in message
...
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

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

"Zack Barresse" wrote:

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


--
Regards,
Zack Barresse, aka firefytr

"learner" wrote in message
...
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

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

"Bill Kuunders" wrote:

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
--
Greetings from New Zealand
Bill K

"learner" wrote in message
...
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

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

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

In article ,
"learner" wrote:

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?

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
"learner" wrote in message
...
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

"Bill Kuunders" wrote:

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
--
Greetings from New Zealand
Bill K

"learner" wrote in message
...
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

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" wrote:

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
"learner" wrote in message
...
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

"Bill Kuunders" wrote:

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
--
Greetings from New Zealand
Bill K

"learner" wrote in message
...
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

That's good.
Thanks for the feedback.

Greetings from New Zealand
Bill K
"learner" wrote in message
...
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" wrote:

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
"learner" wrote in message
...
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

"Bill Kuunders" wrote:

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
--
Greetings from New Zealand
Bill K

"learner" wrote in message
...
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