Averages
Posted by Tanya on October 04, 2001 9:48 AM
Is there a way to do a function to take the average of only the cells that have a number in it? I have a select group of cells, and every week different cells in this group may be zero. I only want to include the cell in the average if it's not zero. Can you do a selective average like this in a formula?
Posted by Barrie Davidson on October 04, 2001 10:00 AM
Tanya, you could try (assumes your data is in A1:A4)
=SUMIF(A1:A4,"<>0",A1:A4)/COUNTIF(A1:A4,"<>0")
Regards,
BarrieBarrie Davidson
Posted by Aladin Akyurek on October 04, 2001 10:37 AM
Try:
=SUM(A1:A100)/MAX(1,COUNTIF(A1:A100,"<>0"))
If you only expects blank cells, zeroes, and positive numbers, change <> to >.
Aladin
=========
Posted by IML on October 04, 2001 10:41 AM
Barrie's formula is the way to go if you don't have any blank cells in your range.
If you do and want to exlude them, you may want to consider using an array formula (hit enter while control and shift are depressed),such as
=SUMIF(A1:A4,"<>0",A1:A4)/COUNTIF(A1:A4,"<>0")
They will return the same numbers with a data set of
{5;9;0;4}
but will be different with
{5;9;blank;4}
good luck
Posted by Barrie Davidson on October 04, 2001 10:41 AM
Nice touch
Great idea using the MAX function in the denominator!
Regards,
Barrie
Posted by IML on October 04, 2001 10:43 AM
Problem 1: I pasted the wrong formula. I meant to past
=AVERAGE(IF(A1:A4,A1:A4))
Problem 2: Use Aladin's formula. It is alway preferable to avoid using array formula if not necessary.
