Friday, July 27, 2012

yogi_Compute Stats For Specified Entities For Data In A Table For Variable Sheet Names In Another Sheet

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #657   Jul 27, 2012     www.energyefficientbuild.com.


user claire graham said:(http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/HG_OXeI_tAM)
How do you find an average using data from 2 separate sheets? 

Hi,
I am working on a Spreadsheet (populated by a form).
I need to generate a sheet (named Analysis) that shows the average age of the users, this information is currently on 2 other separate sheets(named NAM and NPM).

The age information is shown in column I on both sheets.

I entered the following

=AVERAGE('NAM'!I3:I20000;'NPM'!I3:I20000)

which appears to be fine when there is no data, however, when data is entered the following error appears
error: Circular dependency detected.
I also need to show average ages for particular groups, (average male age, female age).
I have been successful in showing averages from data on just one sheet but combining 2 is causing me a headache.
All help would be appreciated.
with thanks Claire
----
Hi Yogia,
Thanks for getting back to me,
Here is the link to the Spreadsheet.

I am concerned with the Sheet named 'Nursery Analysis'.  This needs to reference several other sheets.

Initially I am concerned with Cell B9 on Nursery Analysis.
I would like to find the average age (in months) of all the boys who have completed a Baseline.
This information is found on sheets - BaselineNAM13/14 and BaselineNPM13/14 in columns G (male) columns I (age in months).
Currently there should be 8 male Baseline entries and the average age across both sheets should be 38.5.
This showing cohort information across 2 classes of nursery children.
I hope I have included all the information you require.
---------------------------------------------------------------------------------------------
following is a solution to the problem

No comments:

Post a Comment