I have a column A with years 2010-2015, column B with data. How do I calculate the average for each year and exclude the 0s when calculating average.
Also how do I do the mean, and standard deviation for them?
I have a column A with years 2010-2015, column B with data. How do I calculate the average for each year and exclude the 0s when calculating average.
Also how do I do the mean, and standard deviation for them?
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
Thanks,
Anil Dhawan
Click *Add Reputation to say "Thanks" and don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved) if you are satisfied.
Don't stop when you are tired. STOP when you are done!
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
Audere est facere
I'm not sure how to do that. I have added a spreadsheet as well. Thanks for the reply
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.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
I have attached the file.
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.
Thanks! That works for averages. How about standard deviation?
I noticed there are multiple formulas on excel for standard deviation.
Thanks![]()
Can anyone help with standard deviation please?
Thanks
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?
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
Ah, thats it!
I had to press the control shift and enter to make the formula work. Thanks for the help guys! Much appreciated![]()
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks