Forum Discussion

Copper Contributor

Mar 31, 2024

#NUM! error with chi-squared test statistic

Dear All, I have been following the instructions on https://www.youtube.com/watch?v=BT5x6DQzIOM for executing the chi-squared test in Excel. It has been working well the first few times I've u...

excel

Formulas and Functions

JennyLMace

Copper Contributor

Apr 02, 2024

Sorry Joe, I can't prioritise replying thoroughly right now. #NUM! (or equivalent error in SPSS) was the error I was referring to. One doesn't have to input any formulae in SPSS. You just put in the data and then ask it to work its magic (i.e. select which test you want done). I can confirm it did state "0.00" in SPSS for the p-value. I read elsewhere that this should still be taken as p<0.001 as absolute zero is impossible - as you also said.

Many thanks again 🙂

JoeUser2004

Bronze Contributor

Apr 02, 2024

JennyLMace wrote: "Sorry Joe, I can't prioritise replying thoroughly right now"

I understand. And thanks; I think you answered my questions sufficiently. Since SPSS is aware of all of the data, it probably calculates the chisq statistic with SUM((actual-expected)^2/expected), which works fine in Excel as well, not with an SPSS equivalent of chisq.inv(pValue,df), which is "numerically-challenged" in Excel (wink). And you confirmed that with such a large chisq statistic, SPSS hits the same numerical limitations as Excel, and it returns a zero p-value.

Bottom line: In Excel, do not use CHISQ.INV.RT(pValue, df) to calculate the chisq statistic, if we have the data. It is unreliable. Use the SUM formula instead. Good to know. Thanks again.