Results 1 to 5 of 5

20030611, 23:08 #1
 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.

20030611, 23:21 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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>)

20030612, 01:06 #3
 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.

20030612, 04:47 #4
 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.95Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20030612, 09:00 #5
 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) 2426 to 14.915.1 or are you comparing 1535 to 1416. 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