changing average if cells populated
 

I have a spreadsheet with various values in three columns. Each column is a
different month of the yr. The values in some of the cells are used to
calculate ratios which appear in let's say row 10.

C D E
F
Row 1 2 5 10
Row2 8 9 3

Row 10 =C1/C2*365 =D1/D2*365 =E1/E2*365 in F10 =AVERAGE(C10:E10)
and in column G through all the other months it is currently using that
same average.

If there are no values in the column needed to do the calculation then e.g F
here, then the cell should average the previous months and use that average
for future months. If the cells/ columns for that month do have values then
it should calculate using the relevant formula and then adjust the next
months cell with the new average e.g. G in this case would then average
c10:f10 and not to e10.

How do I use the if function to do this properly. Thanks.

changing average if cells populated
 

Dave,

If I understand you correctly try this on a COPY of your data:

in cell D10:
=IF(COUNT(C1:C2)<2,"",IF(COUNT(D1:D2)=2,D1/D2*365,AVERAGE($C$10:C10)))

and copy along the row as far as necessary.
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Dave" wrote in message
...
I have a spreadsheet with various values in three columns. Each column is a
different month of the yr. The values in some of the cells are used to
calculate ratios which appear in let's say row 10.

C D E
F
Row 1 2 5 10
Row2 8 9 3

Row 10 =C1/C2*365 =D1/D2*365 =E1/E2*365 in F10
=AVERAGE(C10:E10)
and in column G through all the other months it is currently using that
same average.

If there are no values in the column needed to do the calculation then e.g
F
here, then the cell should average the previous months and use that
average
for future months. If the cells/ columns for that month do have values
then
it should calculate using the relevant formula and then adjust the next
months cell with the new average e.g. G in this case would then average
c10:f10 and not to e10.

How do I use the if function to do this properly. Thanks.

changing average if cells populated
 

Thanks.

"Sandy Mann" wrote:

Dave,

If I understand you correctly try this on a COPY of your data:

in cell D10:
=IF(COUNT(C1:C2)<2,"",IF(COUNT(D1:D2)=2,D1/D2*365,AVERAGE($C$10:C10)))

and copy along the row as far as necessary.
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Dave" wrote in message
...
I have a spreadsheet with various values in three columns. Each column is a
different month of the yr. The values in some of the cells are used to
calculate ratios which appear in let's say row 10.

C D E
F
Row 1 2 5 10
Row2 8 9 3

Row 10 =C1/C2*365 =D1/D2*365 =E1/E2*365 in F10
=AVERAGE(C10:E10)
and in column G through all the other months it is currently using that
same average.

If there are no values in the column needed to do the calculation then e.g
F
here, then the cell should average the previous months and use that
average
for future months. If the cells/ columns for that month do have values
then
it should calculate using the relevant formula and then adjust the next
months cell with the new average e.g. G in this case would then average
c10:f10 and not to e10.

How do I use the if function to do this properly. Thanks.

changing average if cells populated
 

You're very welcome.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Dave" wrote in message
...
Thanks.

"Sandy Mann" wrote:

Dave,

If I understand you correctly try this on a COPY of your data:

in cell D10:
=IF(COUNT(C1:C2)<2,"",IF(COUNT(D1:D2)=2,D1/D2*365,AVERAGE($C$10:C10)))

and copy along the row as far as necessary.
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Dave" wrote in message
...
I have a spreadsheet with various values in three columns. Each column
is a
different month of the yr. The values in some of the cells are used to
calculate ratios which appear in let's say row 10.

C D E
F
Row 1 2 5 10
Row2 8 9 3

Row 10 =C1/C2*365 =D1/D2*365 =E1/E2*365 in F10
=AVERAGE(C10:E10)
and in column G through all the other months it is currently using
that
same average.

If there are no values in the column needed to do the calculation then
e.g
F
here, then the cell should average the previous months and use that
average
for future months. If the cells/ columns for that month do have values
then
it should calculate using the relevant formula and then adjust the next
months cell with the new average e.g. G in this case would then average
c10:f10 and not to e10.

How do I use the if function to do this properly. Thanks.