From what you are describing I am not sure that you can assume that your data meets all of the requirements for using ANOVA. Did you look to see if your data is normally distributed in each of the years as it does not sound as if it is. To be on the conservative side you could consider using a Kruskel-Wallis one-way analysis of variance (Nonparametric test) which does not have all the same constraints and I think you can easily do it by hand if it is not available in Excel. You could use the tools at https://www.statisticssolutions.com/kruskal-wallis-test/ as a quick way forward. The K-W test is not as powerful as a parametric ANOVA but in this case I don’t think it is going to be a problem.

It is unclear to me what data you have. It sounds like you might have just 4 numbers: the average admissions per day for each of 4 years. If so, you cannot do ANOVA on those numbers.

Or perhaps you have 61 numbers for each of the four years, a total of 244 data points: the actual number of admissions on each day of the 2-month period in each year. If so, then you can do ANOVA.

I don't do statistical analysis in Excel, so I can't be much help with specifics on that. In general, I would structure the data with years as the 4 columns and 61 rows for the days of your time period.

I don't know what you asked Excel to give you, but it is possible that 6 is the value of the F statistics for an ANOVA, which (if that's what it is), you could look up in an F table online to convert to a p-value. And/or, it may be that the 0 you got is itself a p-value, but the number of decimal places that Excel is reporting is not enough to show the actual, small p-value and Excel is rounding it off to zero. In that case, you should be able to reset the number of decimal places in Excel.

I hope some of the above is helpful. You might do well to post a little more detail about what the data you have looks like.

Hi, yes I have done exactly as you have said - admissions per day, so I have 61 numbers for 4 years and 244 data points. A lot of days in 2020 there were 0 admissions, could this be why it is struggling to get a p-value?

Thank you though, what you have said makes sense and will try it!

Comparing four yearly means takes a one-way analysis of variance. That's right.

As long as Excel knows to treat the zeroes as valid data (not as a "missing value"), they should present no problems for doing anova.

Yes Burke, I have had a look and the zeroes are definitely the problem. Unfortunately my data is zero heavy particularly in the COVID year (some days had no admissions) so Excel just will not agree with it

Does anyone know how to overcome this?

I tried admissions per week but still had zero values.. and admissions per month did work but obviously is analysis of much fewer numbers

Thanks for your interest.

Maybe the zeroes came into Excel as blank fields rather than actual zeroes. If so, you might try manually typing in a zero everywhere Excel is giving you a "Blank Field" warning (or whatever it says).

Describe it more clearly for better understanding. You have the quantitative outcome for ANOVA and 4 independent groups. Right?

Hi Muhammed Ashraful Alam yes, I have quantitative outcome for ANOVA and 4 independent groups.

I have converted it to number of admissions per day over a set 2 month period for 4 years. However, the data is zero heavy particularly in the 2020 group where there have been no admissions on many days. Excel does not seem to recognise the zero values and so is giving me p values of 6.

Is there no admission in 2020? Otherwise you have problem in data entry. Fix it first please.

There are some admissions in 2020, but not every day had admissions. There are many days that have zero values (i.e. no admissions)

From what you are describing I am not sure that you can assume that your data meets all of the requirements for using ANOVA. Did you look to see if your data is normally distributed in each of the years as it does not sound as if it is. To be on the conservative side you could consider using a Kruskel-Wallis one-way analysis of variance (Nonparametric test) which does not have all the same constraints and I think you can easily do it by hand if it is not available in Excel. You could use the tools at https://www.statisticssolutions.com/kruskal-wallis-test/ as a quick way forward. The K-W test is not as powerful as a parametric ANOVA but in this case I don’t think it is going to be a problem.

Christa Br, ANOVA seems like the correct analysis, I have done a quick simulation with excel for 4 columns and 61 rows with lots of zero entries and some missing values. It doesn't seem that zero is a problem in getting the answer!

• I would suggest visualizing your data as well to identify abnormal situations.
• Make sure that you can check assumptions related to residuals after analysis with excel.
• R is open-source and free software for statistical analysis. If this is not a one-time project you may want to consider learning R programming.
• If you wish you can share your data and someone else (myself) can do a quick analysis to validate your findings.
• I have attached the excel file for my simulation.

Good luck!

Could you send your Excel file or your raw data file so I can understand your data? Sounds interesting.

If, as Patrice suggests, you share your data, I am sure that you will get a good set of responses that you can evaluate to decide which is your best way forward.

For this scenario you might do best to start from basics, rather than expecting that your problem can be fitted easily into one of the standard statistical procedures.

Let's assume that you really do need to take an "hypothesis testing" approach. Firstly, consider what it is about the outcome for different years that you want to compare ... is it the mean number of admissions per day, or the median, or perhaps the number of days that admissions exceed a given threshold? The construct a numerical value that compares your selected year's value to that of the others, ... perhaps the difference from the mean or the median of the other years' values. This would lead to the observed value of a, hopefully, meaningful test statistic. Then the problem is to get "null distribution" for the test statistic. If your "null hypothesis" were that the counts are independent between days within a given year, and that there is no difference in distributions of daily counts between years, then you can readily (conceptually at least) get that null distribution by doing a permutation test. That is, you evaluate your chosen test statistic for each of a number of different alternative versions of the data-set, each of which is equally likely to have happened under the null hypothesis. Each alternative version would be created as a random rearrangement of the overall set of daily values into a new set of "years".

If you don't need to start immediately with an "hypothesis testing" approach, then you might think of a graphical analysis, perhaps starting by comparing the histograms for each year. This, itself, might be enough, but it might lead you on to wanting to construct a test statistic based on the histograms. Otherwise it might lead you to choose a distributional model, other than the normal distribution, for further use.

Actually sending even a portion or a pdf of the data would help me understand the structure of the data.

Hi Christa,

Sorry to jump in with a simple observation: did you look at the histogram (raw data, not normalized in any way)? Sometimes this simple thing can solve the underlying problem.

All the best,

George

Hi all, thank you all so much for your input - this is now resolved. It was a problem with the decimal points in Excel, the data did indeed work with ANOVA. My data is very much similar to the example given by Zahra Saki above. Thank you!