aauugghhh...#div/o problems & various average formula problems
 

Help...I'm confusing myself...

Trying to determine an average per month, based on 12 months of numerical
data€¦but some of the cells have zeros. Now€¦I need to divide the sum of
those 12 cells by 12 IF they ALL have numbers greater than 0. If any of the
12 cells has a 0€¦then I need to divide the sum of the 12 cells by the number
of cells that actually have a whole number. I.E.
13 13
2 2
3 0
4 4
5 = 27/5 = 5.4 5 = 24/4 =6 (This is the answer I need)


Been using the array formula: =AVERAGE(IF(I6:I17<0, I6:I17,""))€¦which works
fine€¦. But some of the columns have ALL zeros€¦.and of course I get the
#DIV/0 error, so I tried the =SUM(I6:I17)/COUNTIF(I6:I17,"0") on all the
columns, didnt work (divides the sum of the cells by ALLS including the zero
cells)€¦so I need to use ONE array formula, plus something??? to give me a
zero total on the zero column. Because it affects the formula I use later to
calculate Quarterly averages€¦.(maybe I need to do a format change???)
Yep...I am confused€¦

Whew€¦.anyone can help me with this€¦Id be truly grateful€¦.

aauugghhh...#div/o problems & various average formula problems
 

acbel40,
Try this solution (it worked for me):
In the cell that is for the average, put this formula:
"=(SUMIF(A1:A5,"0")/COUNTIF(A1:A5,"0"))"
A1 = 13
A2 = 2
A3 = 3
A4 = 5
A5 = 5
A6 will be 5.4 (format cell to 1 decimal place).

Both halves of the formula only adds up the numbers larger than 0.

hth

"acbel40" wrote:

Help...I'm confusing myself...

Trying to determine an average per month, based on 12 months of numerical
data€¦but some of the cells have zeros. Now€¦I need to divide the sum of
those 12 cells by 12 IF they ALL have numbers greater than 0. If any of the
12 cells has a 0€¦then I need to divide the sum of the 12 cells by the number
of cells that actually have a whole number. I.E.
13 13
2 2
3 0
4 4
5 = 27/5 = 5.4 5 = 24/4 =6 (This is the answer I need)


Been using the array formula: =AVERAGE(IF(I6:I17<0, I6:I17,""))€¦which works
fine€¦. But some of the columns have ALL zeros€¦.and of course I get the
#DIV/0 error, so I tried the =SUM(I6:I17)/COUNTIF(I6:I17,"0") on all the
columns, didnt work (divides the sum of the cells by ALLS including the zero
cells)€¦so I need to use ONE array formula, plus something??? to give me a
zero total on the zero column. Because it affects the formula I use later to
calculate Quarterly averages€¦.(maybe I need to do a format change???)
Yep...I am confused€¦

Whew€¦.anyone can help me with this€¦Id be truly grateful€¦.

aauugghhh...#div/o problems & various average formula problems
 

"acbel40" wrote:
Been using the array formula: =AVERAGE(IF(I6:I17<0, I6:I17,""))€¦
which works fine€¦. But some of the columns have ALL zeros€¦.and
of course I get the #DIV/0 error

If you know that the size of the range is 12, and all cells contain numeric
values, then:

=if(countif(I6:I17,0)=12, 0, average(if(I6:I17<0, I6:I17)))

More generally:

=if(countif(I6:I17,0)=count(I6:I17), 0, average(if(I6:I17<0, I6:I17)))

Both should be entered as an array formula. That is, commit with
ctrl+shift+Enter instead of Enter.


----- original message -----

"acbel40" wrote in message
...
Help...I'm confusing myself...

Trying to determine an average per month, based on 12 months of numerical
data€¦but some of the cells have zeros. Now€¦I need to divide the sum of
those 12 cells by 12 IF they ALL have numbers greater than 0. If any of
the
12 cells has a 0€¦then I need to divide the sum of the 12 cells by the
number
of cells that actually have a whole number. I.E.
13 13
2 2
3 0
4 4
5 = 27/5 = 5.4 5 = 24/4 =6 (This is the answer I need)


Been using the array formula: =AVERAGE(IF(I6:I17<0, I6:I17,""))€¦which
works
fine€¦. But some of the columns have ALL zeros€¦.and of course I get the
#DIV/0 error, so I tried the =SUM(I6:I17)/COUNTIF(I6:I17,"0") on all the
columns, didnt work (divides the sum of the cells by ALLS including the
zero
cells)€¦so I need to use ONE array formula, plus something??? to give me a
zero total on the zero column. Because it affects the formula I use later
to
calculate Quarterly averages€¦.(maybe I need to do a format change???)
Yep...I am confused€¦

Whew€¦.anyone can help me with this€¦Id be truly grateful€¦.

aauugghhh...#div/o problems & various average formula problems
 

Thank you....but I'm averaging the columns...will this formula work
(substituting SUMIF with AVERAGEIF?

"FloMM2" wrote:

acbel40,
Try this solution (it worked for me):
In the cell that is for the average, put this formula:
"=(SUMIF(A1:A5,"0")/COUNTIF(A1:A5,"0"))"
A1 = 13
A2 = 2
A3 = 3
A4 = 5
A5 = 5
A6 will be 5.4 (format cell to 1 decimal place).

Both halves of the formula only adds up the numbers larger than 0.

hth

"acbel40" wrote:

Help...I'm confusing myself...

Trying to determine an average per month, based on 12 months of numerical
data€¦but some of the cells have zeros. Now€¦I need to divide the sum of
those 12 cells by 12 IF they ALL have numbers greater than 0. If any of the
12 cells has a 0€¦then I need to divide the sum of the 12 cells by the number
of cells that actually have a whole number. I.E.
13 13
2 2
3 0
4 4
5 = 27/5 = 5.4 5 = 24/4 =6 (This is the answer I need)


Been using the array formula: =AVERAGE(IF(I6:I17<0, I6:I17,""))€¦which works
fine€¦. But some of the columns have ALL zeros€¦.and of course I get the
#DIV/0 error, so I tried the =SUM(I6:I17)/COUNTIF(I6:I17,"0") on all the
columns, didnt work (divides the sum of the cells by ALLS including the zero
cells)€¦so I need to use ONE array formula, plus something??? to give me a
zero total on the zero column. Because it affects the formula I use later to
calculate Quarterly averages€¦.(maybe I need to do a format change???)
Yep...I am confused€¦

Whew€¦.anyone can help me with this€¦Id be truly grateful€¦.

aauugghhh...#div/o problems & various average formula problems
 

That was a dumb question...I tried it...but in the column with all zeros...it
gives me the #div/0 error now (works fine on columns with whole numbers)


"FloMM2" wrote:

acbel40,
Try this solution (it worked for me):
In the cell that is for the average, put this formula:
"=(SUMIF(A1:A5,"0")/COUNTIF(A1:A5,"0"))"
A1 = 13
A2 = 2
A3 = 3
A4 = 5
A5 = 5
A6 will be 5.4 (format cell to 1 decimal place).

Both halves of the formula only adds up the numbers larger than 0.

hth

"acbel40" wrote:

Help...I'm confusing myself...

Trying to determine an average per month, based on 12 months of numerical
data€¦but some of the cells have zeros. Now€¦I need to divide the sum of
those 12 cells by 12 IF they ALL have numbers greater than 0. If any of the
12 cells has a 0€¦then I need to divide the sum of the 12 cells by the number
of cells that actually have a whole number. I.E.
13 13
2 2
3 0
4 4
5 = 27/5 = 5.4 5 = 24/4 =6 (This is the answer I need)


Been using the array formula: =AVERAGE(IF(I6:I17<0, I6:I17,""))€¦which works
fine€¦. But some of the columns have ALL zeros€¦.and of course I get the
#DIV/0 error, so I tried the =SUM(I6:I17)/COUNTIF(I6:I17,"0") on all the
columns, didnt work (divides the sum of the cells by ALLS including the zero
cells)€¦so I need to use ONE array formula, plus something??? to give me a
zero total on the zero column. Because it affects the formula I use later to
calculate Quarterly averages€¦.(maybe I need to do a format change???)
Yep...I am confused€¦

Whew€¦.anyone can help me with this€¦Id be truly grateful€¦.

aauugghhh...#div/o problems & various average formula problems
 

Check first...

=if(countif(a1:a5,""&0)=0,"No numbers 0",sumif(...)/countif(...))



acbel40 wrote:

That was a dumb question...I tried it...but in the column with all zeros...it
gives me the #div/0 error now (works fine on columns with whole numbers)

"FloMM2" wrote:

acbel40,
Try this solution (it worked for me):
In the cell that is for the average, put this formula:
"=(SUMIF(A1:A5,"0")/COUNTIF(A1:A5,"0"))"
A1 = 13
A2 = 2
A3 = 3
A4 = 5
A5 = 5
A6 will be 5.4 (format cell to 1 decimal place).

Both halves of the formula only adds up the numbers larger than 0.

hth

"acbel40" wrote:

Help...I'm confusing myself...

Trying to determine an average per month, based on 12 months of numerical
data€¦but some of the cells have zeros. Now€¦I need to divide the sum of
those 12 cells by 12 IF they ALL have numbers greater than 0. If any of the
12 cells has a 0€¦then I need to divide the sum of the 12 cells by the number
of cells that actually have a whole number. I.E.
13 13
2 2
3 0
4 4
5 = 27/5 = 5.4 5 = 24/4 =6 (This is the answer I need)


Been using the array formula: =AVERAGE(IF(I6:I17<0, I6:I17,""))€¦which works
fine€¦. But some of the columns have ALL zeros€¦.and of course I get the
#DIV/0 error, so I tried the =SUM(I6:I17)/COUNTIF(I6:I17,"0") on all the
columns, didnt work (divides the sum of the cells by ALLS including the zero
cells)€¦so I need to use ONE array formula, plus something??? to give me a
zero total on the zero column. Because it affects the formula I use later to
calculate Quarterly averages€¦.(maybe I need to do a format change???)
Yep...I am confused€¦

Whew€¦.anyone can help me with this€¦Id be truly grateful€¦.


--

Dave Peterson