Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Z test for 2 numbers, conditional formatting (Excel xp)

    I need to format cells that are significant at 90% or 95%. I cannot find the exact formula to enter into the conditional formatting. Could someone help me with the formula for the attached. Thank you very much.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Z test for 2 numbers, conditional formatting (Excel xp)

    Put the most stringent condition first: if the cell value is greater than 95%, set the background color to yellow.
    Click Add
    Put the other condition in second place: if the cell value is greater than 90%, set the border to black.

    I don't know where you want to use conditional formatting, since you haven't done any calculations yet (and haven't provided enough information for a Z test, I believe, but I'll let others judge that <img src=/S/grin.gif border=0 alt=grin width=15 height=15>)

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Z test for 2 numbers, conditional formatting (Excel xp)

    oops, sorry. I'm trying to test the Blue column against the norm column. So if 25 is significant to 15 at the 90% conf level the cell would be bordered. Thank you

    I don't know what the formula for a z test is.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Z test for 2 numbers, conditional formatting (Excel xp)

    The ztest formula is (big surprise <g>): =ZTEST(arg1,arg2,arg3)

    Look in Help whether this is the test you're after and what the arguments denote. Use the formula of the ztest as the conditional format formula:

    =ZTEST(arg1,arg2,arg3)>0.9

    and for the second condition:

    =ZTEST(arg1,arg2,arg3)>0.95
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Z test for 2 numbers, conditional formatting (Excel xp)

    If you want to make 4 comparisons: 25 to 15 and 44 to 26 and 27 to 21 and 33 to 34, you are missing the the standard deviation of each of the values of the columns. (I assume the "BASE" is the number of values.)

    Are you comparing (eg at 95 CI) 24-26 to 14.9-15.1 or are you comparing 15-35 to 14-16. In the former they are different in the latter there is NOT.

    The Z value is calculated:

    Z = (Avg1 - Avg2) / Sqrt(Var1/n1 - Var2/n2)
    Where Avg1 is the avg of sample 1
    Var1 is Variance of sample 1
    n1 = number of points of sample 1

    Absolute Values of Z >1.96 are significant at 95%
    Absolute Values of Z >1.645 are significant at 90%

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •