Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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? |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Edit + Find | Excel Discussion (Misc queries) | |||
find a cells from a range of cell | Excel Worksheet Functions | |||
Using the Find tool in EXCEL | Excel Worksheet Functions | |||
Excel has a "Find Next" command but no "Find Previous" command. | Excel Discussion (Misc queries) | |||
VB Find and Replace | Excel Worksheet Functions |