Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
changing average calc if cells populated | Excel Worksheet Functions | |||
Sort cells populated with an Indirect function. | Excel Worksheet Functions | |||
Conditional formulas: color code text populated cells? | Excel Worksheet Functions | |||
Macro Help: Concatenate Populated Cells in Column A | Excel Discussion (Misc queries) | |||
how do populate empty cells with the contents of populated cells . | Excel Discussion (Misc queries) |