Help. I've calculated the average of three different cells in three different
rows that are not in sequential rows. The problem is that some of the cells
may contain a zero so the formula should only divide by two instead of three.
Anyone of the cells could be zero so you may have to divide by one, two or
three. Is there a formula???

TRY ANY ONE OF THE FOLLOWING :
1. =AVERAGE(IF(A1:A7<0, A1:A7))
This is an array formula and as such it must be commited with
Shift+Ctrl+<Enter

2. =SUMIF(A1:A7, "<0")/COUNTIF(A1:A7, "<0")

click yes below, if it works

"Help" wrote:

Help. I've calculated the average of three different cells in three different
rows that are not in sequential rows. The problem is that some of the cells
may contain a zero so the formula should only divide by two instead of three.
Anyone of the cells could be zero so you may have to divide by one, two or
three. Is there a formula???

thanks for the help....I should have been a little clearer
Qtr 1 Qtr 2 Qtr3
A B C D E F G H I Avg
1 3 5 3
6 5 0 5.5 (not 3.6)
0 0 8 8

Columns B, C, E, etc have numbers as well. I'm tring to get an average of
the first quarter over a six year period.

"Help" wrote:

Help. I've calculated the average of three different cells in three different
rows that are not in sequential rows. The problem is that some of the cells
may contain a zero so the formula should only divide by two instead of three.
Anyone of the cells could be zero so you may have to divide by one, two or
three. Is there a formula???

IF YOUR DATA CONTAINS DATA FOR THE FIRST QUARTER IN THE RANGE B1 TO C6, FOR
WHICH U WANT TO CALCULATE AVERAGE,
1. TYPE IN DESIRED CELL
=AVERAGE(IF(B1:C6<0, B1:C6))
This is an array formula and as such it must be commited with
Shift+Ctrl+<Enter

2. =SUMIF(B1:C6, "<0")/COUNTIF(B1:C6, "<0")

ELSE CHANGE THE RANGE SUITABLY

click yes below, if it works

"Help" wrote:

thanks for the help....I should have been a little clearer
Qtr 1 Qtr 2 Qtr3
A B C D E F G H I Avg
1 3 5 3
6 5 0 5.5 (not 3.6)
0 0 8 8

Columns B, C, E, etc have numbers as well. I'm tring to get an average of
the first quarter over a six year period.

"Help" wrote:

Help. I've calculated the average of three different cells in three different
rows that are not in sequential rows. The problem is that some of the cells
may contain a zero so the formula should only divide by two instead of three.
Anyone of the cells could be zero so you may have to divide by one, two or
three. Is there a formula???