Trying to make the Ztest formula work, and normally I know how to but not in this situation, it keeps giving me the #value sign. I'm trying to do a Ztest for a certain array of numbers that excludes some cells. For example.
A1
A2
A3
A4
I am trying to do a ztest only on the values in A1, A2, and A4 while excluding A3. (In actuality, I'm trying to do something like this over 160 cells) The Excel formula and how it works is not letting me exclude A3.
Thanks for any help!
Have you tried selecting just those cells and naming the range?
And then referencing the named range instead?
Never thought of that, I'll give it a try.Originally Posted by daffodil11
Tried it out, it did not work. Tried creating the name and clicked "Use in Formula" but it did not work....
Are you sure you did it right?
Under Formulas, click the Name Manager. Hit New, Name the Range (I called mine zRange), then select the cells you want in that range, and hit okay.
Then somewhere type =ztest(zRange,[xvalue]) and it should work.
Attachment 260288
I ended up doing it right, but now I'm limited to the amount of entries I can put in it. Why does this limit exist?
Do you mean you're limited to the amount of ranges per equation, or the amount of named ranges you can create?
The limitations on the number of named ranges is a function of your computer's memory.
Make Mom proud: Add to my reputation if I helped out!
Make the Moderators happy: Mark the Thread as Solved if your question was answered!
As a quick check, I put some random data into a spreadsheet and included a few text strings in the array. It appears that the Ztest function will ignore text strings in calculating the ztest score. My suggestion would then be to use a helper column =IF(condition for including point in ztest,a3,"not used"). This helper column then becomes the array argument for the z function.
See if that helps.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks