If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
30 Day Moving Average Ignoring Blank Cells
I have a column of data covering 365 days, some of the cells have data and
some don't. I am trying to figure out how to calculate a 30 day moving average ignoring blank cells. In other words, I want the average of the last 30 days that have a data value in the cell. The moving average may have to look back at the last 50 to 60 days in order to get 30 days that have values to average. I would appreciate any suggestions how to calculate this. |
#2
|
|||
|
|||
30 Day Moving Average Ignoring Blank Cells
Hi!
What if there aren't 30 days of data to average? Assume your values are in the range A1:A365 Entered as an array using the key combo of CTRL,SHIFT,ENTER: =AVERAGE(A365:INDEX(A1:A365,LARGE(IF(A1:A365"",R OW(A1:A365)),30))) If there aren't 30 values this version will average all values until there are 30 or more, then it will average the last 30. =AVERAGE(A365:INDEX(A1:A365,LARGE(IF(A1:A365"",R OW(A1:A365)),MIN(COUNT(A1:A365),30)))) Biff "ethatch" wrote in message ... I have a column of data covering 365 days, some of the cells have data and some don't. I am trying to figure out how to calculate a 30 day moving average ignoring blank cells. In other words, I want the average of the last 30 days that have a data value in the cell. The moving average may have to look back at the last 50 to 60 days in order to get 30 days that have values to average. I would appreciate any suggestions how to calculate this. |
#3
|
|||
|
|||
30 Day Moving Average Ignoring Blank Cells
"ethatch" wrote...
I have a column of data covering 365 days, some of the cells have data and some don't. I am trying to figure out how to calculate a 30 day moving average ignoring blank cells. In other words, I want the average of the last 30 days that have a data value in the cell. The moving average may have to look back at the last 50 to 60 days in order to get 30 days that have values to average. I would appreciate any suggestions how to calculate this. If your data were in C5:C369, then you could calculate 30 day moving averages using array formulas like D5: =IF(AND(COUNT(C$5:C5)=30,COUNT(C5)), AVERAGE(INDEX(C$5:C5,MATCH(COUNT(C$5:C5)-29, MMULT(--(ROW(C$5:C5)=TRANSPOSE(ROW(C$5:C5))),--ISNUMBER(C$5:C5)), 0)):C5),"") Fill D5 down into D6369. This will evaluate to "" until you reach the row with the 30th number in col C and on any row thereafter in which col C doesn't contain a number. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
checking that cells have a value before the workbook will close | kcdonaldson | Worksheet Functions | 8 | December 5th, 2005 04:57 PM |
CONCATENATE problem with blank cells | roger_home | General Discussion | 1 | August 17th, 2005 09:18 PM |
Average the Last Five Cells in a Column | Warrior Princess | Worksheet Functions | 3 | March 16th, 2005 02:12 PM |
geomean ignoring blank cells and chars | Stan Altshuller | Worksheet Functions | 1 | January 12th, 2005 09:21 PM |
Ignoring Blank Cells | Eric | Worksheet Functions | 1 | June 18th, 2004 09:59 PM |