Moving average excluding blank cells

HI, i am new to the site and was hoping for some help.

I have a column of data that contains various blank cells where no data was measured. In the adjacent column I want to take the moving average of the last 4 data points including the most recent entry. My problem is i do not know how to handle blank cells where there was no data. I need it to average the last four in the column where data acutally exists. I am ok with using helper cells if needed and I am not worried about the first four results at this time.

Below is what i would like to see


Example data

A..................B
15
50
25
20................55
Blank............55
30................31.25
35................27.5
blank............27.5
blank............27.5
15................25
10................22.5
15................18.75
40................20
blank.............20

Thanks for any help.

Thanks for the response. That simple solution would work on the first four values because there are no blanks, but as you go down the the column there me be a blank in the range. The problem is i always need the average to include the last four legitimate data points. So if there is two blanks it will need to go up six places to get four real values, five spots if one blank etc..

Originally Posted by mrice

Try this user defined function

Please Login or Register  to view this content.

Thanks for the help. This almost works except it counts down the sheet instead of taking the four "previous values". I tried modifying where I start the reference cell but didn't work. I am not very good with VB code, is there a way to modify this to make the range take the four previous values instead of starting with the reference and counting down the column?

Thanks again for any help.

I tried this modificationand it seems to work. Thanks so much for the help! i am sure i will have many more questions to post on this site.


Function MoveAve(StartCell As Range)
Counter = 0
Do While ValidCounter < 4
MySum = MySum + StartCell.Offset(-1 * Counter, 0)
If StartCell.Offset(-1 * Counter, 0) <> "" Then ValidCounter = ValidCounter + 1
Counter = Counter + 1
Loop
MoveAve = MySum / 4
End Function

Hi,

Assuming, that you data starts from A2, put this array-formula in B2:

=SUM(INDIRECT("A"&LARGE(($A$2:A2<>"")*ROW($A$2:A2),4)&":A"&ROW()))/4

and copy it down for as long as needed. Because it is an array formula, you should enter it by pressing Ctrl+Shift+Enter. For the first three cell it gives an error, but after fourth value it should work just fine.

- Asser

Both of the above worked. Thanks Mrice and jazzer

Jazzer, for learning purposes can you explain that formula out for me?

Thanks

Thanks so much for the explanation and help. It is greatly appreciated.

This was of great help. Thanks to all.

In addition to a non-blank moving average, I would like to compute an average of the last 10 of my first-of-the-day, i.e.fasting, blood sugar readings. Currently I signify this by drawing a bottom border under the last reading of the previous day. Something like this:


8/23/2011 5:15 AM 69
5:00 PM 139
8/24/2011 5:30 AM 86
5:00 PM 199
8/25/2011 6:00 AM 70
5:00 PM 224
8/26/2011 5:15 AM 102
4:45 PM 179
8/27/2011 5:30 AM 66

Well, I couldn't replicate the underlining, but I hope you get the idea.

Is there any way to incorporate this kind of formatting into the previous Visual Basic formula? Or is there some creative way I could use the time (second column) as a parameter to control which cells to count?

Thanks in advance for any help.
4:45 PM 144