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

1. 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. 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.
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. 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. 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

5. 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
•