yogi_Compute Average For The Last Complete Quarter From The Posted Data

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #685   Aug 11, 2012     www.energyefficientbuild.com.

user Dave Lalande said:
Quarterly Averages
I have a sheet where I draw gauges and trending charts for Intranet embedding.  We started out with just the months and now I being asked to post a YTD average and a Last Complete Quarter Average.

Here is a copy of the sheet I'm currently using.  This is a copy so feel free to tinker.

https://docs.google.com/spreadsheet/ccc?key=0Ai_2YLvaQba0dGthVG9uMFU5WDRPX25zS1Rqbm5xU1E#gid=19 

Row 25 is where I need to average, highlighted in yellow.

I can easily do this if I used a separate cell for the averages, but since I'm counting on a single cell to draw embedded gauges and charts, I need a more complex formula.

Thanks for any help you can lend.

Dave

----

Hi Yogi,

Thanks for looking at this for me.

It is on the Metric sheet B25 that I wanted to find the averages for the last complete quarter collected.  We're collecting performance data throughout the fiscal year which starts in October.  So fo we wouldn't want an average until we collect Oct-Dec, 2012 and then I was hoping that when we collect Jan-Mar 2013 (I'll be changing the sheet column descriptions for the new year in the production sheets) then the average number would change to the Jan.-mar. average. Until we have March #'s the average would be for October 2012 - December 2012 (the last completely collected quarter) and so on with April - June 2013, the average in B25 would be for Jan-Mar until we have Junes #.

I could do this by breaking out the numbers in additional cells, but I'm going to use the number in B25 to draw a gauge to embed in an Intranet page, so I really need to count on B25 to be the number as well as use it in trending charts.  You can see the gauge and trending chart examples I'm embedding now in the sheet.

Does this make sense?

About the import range.  I am importing from a sheet in a workbook separate from this one because the Directors are entering their numbers there.  I didn't want them in this sheet entering each month because they could blow things up.  It's work well having them enter in a separate sheet thus far.

We piloted the system as it is now last year with 5 departments and it went GREAT.  So I'm going to roll it out to about 20+ departments and I'm being asked to and gauges for YTD average and a last quarter fully collected average as well.  This way we can see (gauges) current month along side of last quarter and YTD.

----------------------------------------------------------------------------------------

following is my convoluted solution to the problem