LinkBack URL
About LinkBacks
I have a list of numbers and i want to calculate avg and stdev but i do not want "zeros" or "blanks" to enter into the calculations. I have a formula for avg but i need one for stdev.
I have a list of numbers and i want to calculate avg and stdev but i do not want "zeros" or "blanks" to enter into the calculations. I have a formula for avg but i need one for stdev.
Last edited by welchs101; 12-12-2008 at 07:20 PM.
If you have a formula that works for AVERAGE, just change AVERAGE to STDEV.
Entia non sunt multiplicanda sine necessitate
formula for avg:
SUM(A1:A5)/COUNTIF(A1:A5,">0")
Cant just replace avg with stdev
=stdev(if(a1:a5>0, a1:a5))
That is an array formula, though.
Yeah, its hard to find a formula with out using the array calc.
You could do it with a helper column: =if(a1>0, (a1-avg)^2, ""), where avg is the value you computed with your other formula.
Then St Dev =sqrt(sum(B1:B5) / (count(b1:b5) - 1) )
Why the aversion to array formulas?
Last edited by shg; 12-12-2008 at 06:59 PM.
I have used array formulas in the past and they mess me up sometimes. I also remember reading somewhere that there are certain rules you must follow so you dont get an error when using array formulas......do you know what they are?
for got to say thanks for replying.
You're welcome.
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
Edit: I'm not aware of any bugs with array formulas. They should be used thoughtfully, because they can be compute-intensive.
Then only rule I know of (aside from ensuring that arrays match) is that they need to be confirmed with Ctrl+Shift+Enter.
If you don't use them at all, you're missing one of Excel's most powerful features.
Last edited by shg; 12-12-2008 at 07:15 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks