Using AVERAGEIFS to avg by month, if none found, avg last 3 good set

Please Login or Register  to view this content.

Using the above code I am looking at data that gets entered in on a random basis. We may have something to enter in one month while others we may not. Column A is the dates while Column B is the data and then it is looking at L423 to get the start date of the month and then uses the end of month formula to get an ending date for the month.

I have attached a photo which may help illustrate what I am wanting to do. Like I said, data gets entered in if there is any, then the formula gives us a monthly average elsewhere in the workbook, however, if a particular month doesn't have any data I would like it then to average the last 3 monthly averages. (again look at the picture as it may help illustrate.) Capture.JPG

Anybody know of a formula that may be able to do this?

Here is a sample workbook.

The count feature would be great, however, I don't want it to average other "averaged data." It needs to average the last 3 GOOD averages of actual data.

For instance if I have information for January, February, & March but don't have anything for April, April would be the average of January, February, & March and then say I have something for May but not June, then the average for June would be February, March, May since April was an average of J, F, M. Make sense?

Sweet deal! It is a step closer, however, it is looking at the wrong column for if no information is found. It needs to pull the last three monthly averages were as the info table is just daily averages so you are averaging the last three daily. I have re uploaded your sheet and made some corrections to my original data. Column I and J should match K and L exactly. K and L are what we actually reported and manually figured out.

Another example, 7/1/2014 - 7/31/2014 has no daily data so it can't create a monthly average in cell I15 however 4/1/2014 - 6/1/2014 do have daily data which gives a monthly average in cell I12, I13, and I14. So I15 would be the average of I12, I13, and I14. HOWEVER, we don't have any data again until 1/1/2015 - 1/31/2015 (I21) and none in 2/1/2015 - 2/28/2015 (I22) so I22 would be the average of I21, I14, and I13 since those are the last three true averages. I hope that makes sense.