Monthly averages help

[richbauer]

I have a spreadsheet where I would like to average the monthly income in column E for only months entered ( lets say it only has listed up to march so far). But be able to include April if I add it and so on. If it is zero for the month I still want to include it on the average. Then have the sum of the monthly average multiply for yearly projection.

What formula would I use for Excel 2008?


http://speedy.sh/cCC82/Acme-IncYTDFIN.xlsx

 

[NARAYANK991]

Hi ,


You can do this using named ranges ; if you define a range any_name_of_your_choice , using the following Refers To formula :


=OFFSET(Sheet1!$E:$E,1,0,COUNTA(Sheet1!$E:$E)-4)


will ensure that the rows which have data are used in the calculations.


After defining the range , as mentioned above , use it in your average calculation as follows :


=SUM(any_name_of_your_choice)/ROWS(any_name_of_your_choice) will divide the SUM by 3 , since only 3 rows of data have been entered ; when you do enter data for April , the SUM will be divided by 4.


Note that if you enter a 0 value for a month , it will still be included in the average calculation ; but if you leave any month entry blank , that month will not be included in the calculation.


Also note that I have subtracted 4 from the COUNTA value because there is some text in the bottom half of your first page ( rows 33 , 34 and 35 ) ; if you move this text out of this column , then the value to be subtracted will change from 4 to 1.


Narayan