On sheet 1 (daily) column C=A*B, for rows corresponding to days 1-31.Some
days data is not entered for A or B, but the formula is still in C. On sheet
2 (averages) I want to calculate the weekly (day 1-7, day 8-14 etc.) averages
for C (sheet 1). The standard AVERAGE function calculates using zero values
for the empty cells from C.Example:
Sheet 1(daily)
ROW A B C
1 1 1 2
2 2 2 4
3
4 3 3 9
5
6
7

Sheet 2(averages)

Row A
1 2.14

sheet 2, A1 should return a 5 (2+4+9)/3 for the average but it calculates
the average as (2+4+0+9+0+0+0)/7 which = 2.14. I dont seem to be able to the
proper function inserted. I don't want to remove the formula every time in C
to make it work. Any ideas? Thanks



--
JD

in c, put, =if((A*B)=0,"",A*B)
This would put a null cell in C instead of a 0. Should force it to skip.

"JD" wrote:

On sheet 1 (daily) column C=A*B, for rows corresponding to days 1-31.Some
days data is not entered for A or B, but the formula is still in C. On sheet
2 (averages) I want to calculate the weekly (day 1-7, day 8-14 etc.) averages
for C (sheet 1). The standard AVERAGE function calculates using zero values
for the empty cells from C.Example:
Sheet 1(daily)
ROW A B C
1 1 1 2
2 2 2 4
3
4 3 3 9
5
6
7

Sheet 2(averages)

Row A
1 2.14

sheet 2, A1 should return a 5 (2+4+9)/3 for the average but it calculates
the average as (2+4+0+9+0+0+0)/7 which = 2.14. I dont seem to be able to the
proper function inserted. I don't want to remove the formula every time in C
to make it work. Any ideas? Thanks



--
JD

Just in case someday A*B might equal zero, you can use:

=SUM(C1:C7)/COUNTIF(C1:C7,"<"&"")
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"JD" wrote in message
...
On sheet 1 (daily) column C=A*B, for rows corresponding to days 1-31.Some
days data is not entered for A or B, but the formula is still in C. On
sheet
2 (averages) I want to calculate the weekly (day 1-7, day 8-14 etc.)
averages
for C (sheet 1). The standard AVERAGE function calculates using zero values
for the empty cells from C.Example:
Sheet 1(daily)
ROW A B C
1 1 1 2
2 2 2 4
3
4 3 3 9
5
6
7

Sheet 2(averages)

Row A
1 2.14

sheet 2, A1 should return a 5 (2+4+9)/3 for the average but it calculates
the average as (2+4+0+9+0+0+0)/7 which = 2.14. I dont seem to be able to the
proper function inserted. I don't want to remove the formula every time in C
to make it work. Any ideas? Thanks



--
JD

If you want to distinguish between blank cells and actual zero values, try
=IF(COUNT(A1:B1)=2,A1*B1,"")
--
David Biddulph

"Sean Timmons" wrote in message
...
in c, put, =if((A*B)=0,"",A*B)
This would put a null cell in C instead of a 0. Should force it to skip.

"JD" wrote:

On sheet 1 (daily) column C=A*B, for rows corresponding to days 1-31.Some
days data is not entered for A or B, but the formula is still in C. On
sheet
2 (averages) I want to calculate the weekly (day 1-7, day 8-14 etc.)
averages
for C (sheet 1). The standard AVERAGE function calculates using zero
values
for the empty cells from C.Example:
Sheet 1(daily)
ROW A B C
1 1 1 2
2 2 2 4
3
4 3 3 9
5
6
7

Sheet 2(averages)

Row A
1 2.14

sheet 2, A1 should return a 5 (2+4+9)/3 for the average but it calculates
the average as (2+4+0+9+0+0+0)/7 which = 2.14. I dont seem to be able to
the
proper function inserted. I don't want to remove the formula every time
in C
to make it work. Any ideas? Thanks



--
JD

Thanks Sean

RagDyer, it still included 0 values for the blank cells.?
--
JD


"JD" wrote:

On sheet 1 (daily) column C=A*B, for rows corresponding to days 1-31.Some
days data is not entered for A or B, but the formula is still in C. On sheet
2 (averages) I want to calculate the weekly (day 1-7, day 8-14 etc.) averages
for C (sheet 1). The standard AVERAGE function calculates using zero values
for the empty cells from C.Example:
Sheet 1(daily)
ROW A B C
1 1 1 2
2 2 2 4
3
4 3 3 9
5
6
7

Sheet 2(averages)

Row A
1 2.14

sheet 2, A1 should return a 5 (2+4+9)/3 for the average but it calculates
the average as (2+4+0+9+0+0+0)/7 which = 2.14. I dont seem to be able to the
proper function inserted. I don't want to remove the formula every time in C
to make it work. Any ideas? Thanks



--
JD

What formuls did you decide to use in Column C?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"JD" wrote in message
...
Thanks Sean

RagDyer, it still included 0 values for the blank cells.?
--
JD


"JD" wrote:

On sheet 1 (daily) column C=A*B, for rows corresponding to days 1-31.Some
days data is not entered for A or B, but the formula is still in C. On
sheet
2 (averages) I want to calculate the weekly (day 1-7, day 8-14 etc.)
averages
for C (sheet 1). The standard AVERAGE function calculates using zero
values
for the empty cells from C.Example:
Sheet 1(daily)
ROW A B C
1 1 1 2
2 2 2 4
3
4 3 3 9
5
6
7

Sheet 2(averages)

Row A
1 2.14

sheet 2, A1 should return a 5 (2+4+9)/3 for the average but it calculates
the average as (2+4+0+9+0+0+0)/7 which = 2.14. I dont seem to be able to
the
proper function inserted. I don't want to remove the formula every time
in C
to make it work. Any ideas? Thanks



--
JD