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" <learner@discussions.microsoft.com> wrote in message
news:8A47E5C1-06BE-4B0B-A0A8-29CF1D55E655@microsoft.com...
>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" <learner@discussions.microsoft.com> wrote in message
> news:8A47E5C1-06BE-4B0B-A0A8-29CF1D55E655@microsoft.com...
> >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 <33D67B10-DC92-468C-8894-305C3F425880@microsoft.com>,
"learner" <learner@discussions.microsoft.com> 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 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" <learner@discussions.microsoft.com> wrote in message
news:8A47E5C1-06BE-4B0B-A0A8-29CF1D55E655@microsoft.com...
>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" <learner@discussions.microsoft.com> wrote in message
> news:8A47E5C1-06BE-4B0B-A0A8-29CF1D55E655@microsoft.com...
> >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 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" <learner@discussions.microsoft.com> wrote in message
news:FE10DBF3-01D3-46A0-BBC4-5E6AE4D71544@microsoft.com...
> 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" <learner@discussions.microsoft.com> wrote in message
>> news:8A47E5C1-06BE-4B0B-A0A8-29CF1D55E655@microsoft.com...
>> >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" <learner@discussions.microsoft.com> wrote in message
> news:FE10DBF3-01D3-46A0-BBC4-5E6AE4D71544@microsoft.com...
> > 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" <learner@discussions.microsoft.com> wrote in message
> >> news:8A47E5C1-06BE-4B0B-A0A8-29CF1D55E655@microsoft.com...
> >> >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" <learner@discussions.microsoft.com> wrote in message
news:7C3AB404-F46E-463A-A720-EE58088E753F@microsoft.com...
> 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" <learner@discussions.microsoft.com> wrote in message
>> news:FE10DBF3-01D3-46A0-BBC4-5E6AE4D71544@microsoft.com...
>> > 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" <learner@discussions.microsoft.com> wrote in message
>> >> news:8A47E5C1-06BE-4B0B-A0A8-29CF1D55E655@microsoft.com...
>> >> >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
>> >>
>> >>
>> >>
>>
>>
>>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks