Calculate average and standard deviation for each year and exclude 0s

Hi Excelas,

You must have Years mentioned somewhere to calculate against each year so you should now for which year you are calculating average.

you can use averageifs or averageif depending on how many criteria you may have later on.


Cheers!!!

Anil Dhawan

Originally Posted by excelas88

Also how do I do the mean, and standard deviation for them?

Mean is usually a synonym for average. If that's not your intent then please define "mean".

If you want standard deviation for a specific year, excluding zeroes, then you can use an array formula like this:

=STDEV(IF(A$2:A$100=D2,IF(B$2:$B100<>0,B$2:B$100)))

confirm with CTRL+SHIFT+ENTER

where D2 contains a specific year

You can replace STDEV with AVERAGE in that formula for an average with the same conditions or use AVERAGEIFS as suggested

Can you post a SMALL sample file and show us what result you expect?

A SMALL file will have about 20 rows worth of data.

For Average, lets assume you have table summary starting from I1. and I2 to going down you have years mentioned.

e.g.

I2 = 2009 J2 = "=AVERAGEIFS(E:E,A:A,I2,E:E,"<>0")"
I3 = 2010 J3 = "=AVERAGEIFS(E:E,A:A,I3,E:E,"<>0")"


Hope this will solve your purpose of Average.

Hey - I guess in Post 3 you have got the Standard Deviation result? Is that not working?? Sorry i did not test it but can you please check once and confirm if that works or not?

Originally Posted by excelas88

Yes I tried it and it did not work.

What was the problem?

The suggested formula is an "array formula" and you need to confirm with CTRL+SHIFT+ENTER

To do that put the formula in a cell, select that cell then press F2 key to slect formula and hold down CTRL and SHIFT keys while pressing ENTER. If done correctly you will see curly braces like { and } around the formula in the formula bar

Yeah I was also thinking why it is not working whereas the function seems to be correct.

Now, please marke this thread as SOLVED and say Thanks to those who gave you solution by adding Add Reputation on left side of the page.


Cheers!!!
Anil