Excel Amateur Struggling with Average Function.

[jouannyr]

Hi all, I'm an Amateur when it comes to using Excel and have been asked to report statistics of the voluntary/charitable service we provide and how we improve people's independence.

We do 2 assessments of a person - one before our service, and another after it (Assessment 1 and Assessment 2). They are assessed over varying different criteria (Nutrional needs, general wellbeing, finance etc..) and given a rating of 1-10.

I would like to report on the average improvement of each criteria (nutritional needs, general wellebeing, finance etc..) between the 1st and 2nd assessments of all our clients who have had the service.

It will be a working spreadsheet with more and more values added as each week progresses and needs to accomodate this.

This needs to be very simple to use as the people who will be using it know even less about Excel than I do!

I have made a start and input all the data but given that I've hit a brick-wall and don't really know what I'm doing I'm not sure sharing it would be helpful! I had got as far as using the AVERAGEIF function to try and calculate the average of each criteria (nutritional needs, general wellbeing, finance etc...) for all the 1st Assessments, then do the same for all the 2nd Assessments and compare the results. But the formula wouldn't do what I wanted it to do (which is obviously my fault).

Any help or assistance would be massivley appreciated as I'm stuck!

Thanks.

 

[baitmaster]

welcome to the board

Whatever you want to calculate is definitely possible, and Im sure you'll get your answers here

For me, Im not entirely sure what you are trying to calculate. I cant see why you would want to use averageif to see how an individual value has changed. Im also not 100% that I understand the structure of your data, which may be the issue

Am I right in thinking your data is a single table, containing multiple people's results (in any particular order), for exactly 2 assessments (rows of data) each?

For the categories stated (wellbeing etc) are there only one category with this header, or multiple measurements within each category - e.g. mental wellbeing, physical welbeing both falling into the wellbeing group?

 

[jouannyr]

Thanks for the reply baitmaster.

Yes it's a single table which has multiple people's results, which basically get input in the order that they arrive on my desk for 2 assessments each.

I've probably used AVERAGEIF mistakenly... I thought I would need to use it in order to differentiate between getting all the criteria results from Assessment1, and then again all criteria results for the 2nd assessment.

There is only one category of each type as well, to clarify every category is: (Nutritional Needs, General Wellbeing, Finance, Social Networks, Community, Medication, Mobility, DLA) and there are no sub-categories at all.

The layout of my table is as follows:

<table border="0" cellpadding="0" cellspacing="0" width="601"><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2377;width:49pt" width="65"> <col style="width:48pt" span="3" width="64"> <col style="mso-width-source:userset;mso-width-alt:2889;width:59pt" width="79"> <col style="mso-width-source:userset;mso-width-alt:2669;width:55pt" width="73"> <col style="width:48pt" span="2" width="64"> <tbody><tr style="mso-height-source:userset;height:39.0pt" height="52"> <td class="xl25" style="height:39.0pt;width:48pt" height="52" width="64"> </td> <td class="xl26" style="border-left:none;width:49pt" width="65">Nutritional Needs</td> <td class="xl26" style="border-left:none;width:48pt" width="64">General Wellbeing</td> <td class="xl27" style="border-left:none;width:48pt" width="64">Finance</td> <td class="xl26" style="border-left:none;width:48pt" width="64">Social Networks</td> <td class="xl26" style="border-left:none;width:59pt" width="79">Community, Church, Groups</td> <td class="xl26" style="border-left:none;width:55pt" width="73">Medication</td> <td class="xl26" style="border-left:none;width:48pt" width="64">Mobility</td> <td class="xl26" style="border-left:none;width:48pt" width="64">Daily Living Activities</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;border-top:none" height="17">1st Asmt</td> <td class="xl28" style="border-top:none;border-left:none">8</td> <td class="xl28" style="border-top:none;border-left:none">4</td> <td class="xl28" style="border-top:none;border-left:none">10</td> <td class="xl28" style="border-top:none;border-left:none">5</td> <td class="xl28" style="border-top:none;border-left:none">5</td> <td class="xl28" style="border-top:none;border-left:none">10</td> <td class="xl28" style="border-top:none;border-left:none">5</td> <td class="xl28" style="border-top:none;border-left:none">5</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;border-top:none" height="17">2nd Asmt</td> <td class="xl28" style="border-top:none;border-left:none">9</td> <td class="xl28" style="border-top:none;border-left:none">10</td> <td class="xl28" style="border-top:none;border-left:none">10</td> <td class="xl28" style="border-top:none;border-left:none">6</td> <td class="xl28" style="border-top:none;border-left:none">6</td> <td class="xl28" style="border-top:none;border-left:none">10</td> <td class="xl28" style="border-top:none;border-left:none">8</td> <td class="xl28" style="border-top:none;border-left:none">7</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl25" style="height:12.75pt;border-top:none" height="17">1st Asmt</td> <td class="xl29" style="border-top:none;border-left:none">8</td> <td class="xl29" style="border-top:none;border-left:none">7</td> <td class="xl29" style="border-top:none;border-left:none">9</td> <td class="xl29" style="border-top:none;border-left:none">9</td> <td class="xl29" style="border-top:none;border-left:none">6</td> <td class="xl29" style="border-top:none;border-left:none">10</td> <td class="xl29" style="border-top:none;border-left:none">10</td> <td class="xl29" style="border-top:none;border-left:none">3</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl25" style="height:12.75pt;border-top:none" height="17">2nd Asmt</td> <td class="xl29" style="border-top:none;border-left:none">10</td> <td class="xl29" style="border-top:none;border-left:none">10</td> <td class="xl29" style="border-top:none;border-left:none">9</td> <td class="xl29" style="border-top:none;border-left:none">9</td> <td class="xl29" style="border-top:none;border-left:none">6</td> <td class="xl29" style="border-top:none;border-left:none">10</td> <td class="xl29" style="border-top:none;border-left:none">10</td> <td class="xl29" style="border-top:none;border-left:none">8</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;border-top:none" height="17">1st Asmt</td> <td class="xl28" style="border-top:none;border-left:none">9</td> <td class="xl28" style="border-top:none;border-left:none">6</td> <td class="xl28" style="border-top:none;border-left:none">6</td> <td class="xl28" style="border-top:none;border-left:none">6</td> <td class="xl28" style="border-top:none;border-left:none">0</td> <td class="xl28" style="border-top:none;border-left:none">9</td> <td class="xl28" style="border-top:none;border-left:none">5</td> <td class="xl28" style="border-top:none;border-left:none">5</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;border-top:none" height="17">2nd Asmt</td> <td class="xl28" style="border-top:none;border-left:none">9</td> <td class="xl28" style="border-top:none;border-left:none">9</td> <td class="xl28" style="border-top:none;border-left:none">6</td> <td class="xl28" style="border-top:none;border-left:none">8</td> <td class="xl28" style="border-top:none;border-left:none">0</td> <td class="xl28" style="border-top:none;border-left:none">9</td> <td class="xl28" style="border-top:none;border-left:none">7</td> <td class="xl28" style="border-top:none;border-left:none">8</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl25" style="height:12.75pt;border-top:none" height="17">1st Asmt</td> <td class="xl29" style="border-top:none;border-left:none">6</td> <td class="xl29" style="border-top:none;border-left:none">5</td> <td class="xl29" style="border-top:none;border-left:none">8</td> <td class="xl29" style="border-top:none;border-left:none">4</td> <td class="xl29" style="border-top:none;border-left:none">5</td> <td class="xl29" style="border-top:none;border-left:none">5</td> <td class="xl29" style="border-top:none;border-left:none">4</td> <td class="xl29" style="border-top:none;border-left:none">3</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl25" style="height:12.75pt;border-top:none" height="17">2nd Asmt</td> <td class="xl29" style="border-top:none;border-left:none">9</td> <td class="xl29" style="border-top:none;border-left:none">8</td> <td class="xl29" style="border-top:none;border-left:none">8</td> <td class="xl29" style="border-top:none;border-left:none">4</td> <td class="xl29" style="border-top:none;border-left:none">7</td> <td class="xl29" style="border-top:none;border-left:none">8</td> <td class="xl29" style="border-top:none;border-left:none">7</td> <td class="xl29" style="border-top:none;border-left:none">6</td> </tr> </tbody></table>

I hope this clarifies what I'm asking....

 

[Yahya]

HI
i don't know if this will help you

Excel Workbook
	A	B	C	D	E	F	G	H	I
1		Nutritional Needs	General Wellbeing	Finance	Social Networks	Community, Church, Groups	Medication	Mobility	Daily Living Activities
2	1st Asmt	8	4	10	5	5	10	5	5
3	2nd Asmt	9	10	10	6	6	10	8	7
4	1st Asmt	8	7	9	9	6	10	10	3
5	2nd Asmt	10	10	9	9	6	10	10	8
6	1st Asmt	9	6	6	6	0	9	5	5
7	2nd Asmt	9	9	6	8	0	9	7	8
8	1st Asmt	6	5	8	4	5	5	4	3
9	2nd Asmt	9	8	8	4	7	8	7	6
10
11	1st Asmt	7.75	5.5	8.25	6	4	8.5	6	4
12	2nd Asmt	9.25	9.25	8.25	6.75	4.75	9.25	8	7.25
Sheet10

Excel 2007

Cell Formulas
Range	Formula
B11	=AVERAGEIF($A$2:$A$9,$A11,B$2:B$9)

 

[jouannyr]

Hi Yahya,

They are the exact results I am hoping to achieve, and perfectly laid-out for what I want..........but unfortunately using that formula returns a #NAME? error. Is it worth mentioning that I'm using Excel 2003 (SP3)?

 

[baitmaster]

I think averageif is a new formula in XL07. Break it down into sumif()/countif() instead, you'll get the right result

 

[Yahya]

in excel 2003
try this

Excel Workbook
	A	B	C	D	E	F	G	H	I
1		Nutritional Needs	General Wellbeing	Finance	Social Networks	Community, Church, Groups	Medication	Mobility	Daily Living Activities
2	1st Asmt	8	4	10	5	5	10	5	5
3	2nd Asmt	9	10	10	6	6	10	8	7
4	1st Asmt	8	7	9	9	6	10	10	3
5	2nd Asmt	10	10	9	9	6	10	10	8
6	1st Asmt	9	6	6	6	0	9	5	5
7	2nd Asmt	9	9	6	8	0	9	7	8
8	1st Asmt	6	5	8	4	5	5	4	3
9	2nd Asmt	9	8	8	4	7	8	7	6
10
11
12	1st Asmt	7.75	5.5	8.25	6	4	8.5	6	4
13	2nd Asmt	9.25	9.25	8.25	6.75	4.75	9.25	8	7.25
Sheet1

Excel 2007

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[jouannyr]

Thanks again for the replies guys.

I've used the formula you have provided me Yahya and it's worked great thanks!

I only have one other thought, as time goes by and more and more data gets added into the spreadsheet (by using Insert to put new lines in), will the formula amend itself to include the new data or should I just substitute the relevant numbers myself whenever I need to produce a report (i.e change it from =AVERAGE(IF($A$2:$A$9=$A12,B$2:B$9)) to say, =AVERAGE(IF($A$2:$A$100=$A103,B$2:B$100)) for example, to include all the extra lines of data...

Thanks again, you've been a great help.

 

[baitmaster]

In your example shown, I would personally colour row 10 black and use it as a bottom border to my table

No data appears in it, and it tells me to insert rows above it. Since formulas would then be written to include row 10, they would always remain correct when rows are inserted