Calculating the average of a column of cells that are populated by a formula

[newbiecel]

Hello, I am new and have no idea what I am asking but we all have to start somewhere so here goes...

I have rows of test scores for different students that at the end in a column are calculated to give me the average for each row

so there is an average function in each of the cells in that column

I would like to know the average score for that end column

So to recap...

I have rows with scores

At the end of the row a cell has the average function to calculate the average score for all the test results in that row

Now that column with the average result - I want at the bottom of the column a cell that displays the average from the row result averages

Can anyone give me a simple way to do this?

Thanks

 

[Eric W]

Welcome to the MrExcel Board.

An average of averages usually won't give you the right result, unless each individual average contains the same number of included items. If that's so, you can just use another AVERAGE function based on the bottom row. If not, you can use an AVERAGE function using the entire original range, something like:

=AVERAGE(B2:Z20)

 

[newbiecel]

Thanks for the quick reply...

Sadly your first point of not using the "same number of included items" is true so your formula just returns an error

Can I copy the results from the row average cells to another group of cells and then use those to find the average?

 

[Eric W]

At this point, I'm having trouble picturing what your sheet looks like. This is my best guess:

	A	B	C	D	E	F	G	H	I	J
Name
Test 1	Test 2	Test 3	Test 4	Test 5	Average	Combined Average
Amy
Bob
Carlos
Dierdre
Ed
Felice

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]99[/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]95[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"][/TD]
[TD="align: right"]90.4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]81.75[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]88[/TD]
[TD="align: right"][/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]87[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]77[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"]98[/TD]
[TD="align: right"][/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]88[/TD]
[TD="align: right"][/TD]
[TD="align: right"]87.66667[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]66[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"][/TD]
[TD="align: right"]86[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]66[/TD]
[TD="align: right"][/TD]
[TD="align: right"]100[/TD]
[TD="align: right"][/TD]
[TD="align: right"]63[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"]70[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]70[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H3[/TH]
[TD="align: left"]=AVERAGE(B3:F3)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J3[/TH]
[TD="align: left"]=AVERAGE(B3:F8)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



If this isn't right, let me know what it does look like, and how you want to see the results.

 

[newbiecel]

Hi Eric

H3 to H8 is what I have now for each row so I can see the average for each students tests

B9 to F9 I have the average for each column so I know the average scores for the tests

Now image Imagine H9

There i want to show the average of the average for the rows average results - H3 to H8

I hope that is clear

Thanks, Gibby

 

[Eric W]

Put the H3 formula in and copy it down to H8. Take the formula I have in J3 and move it to H9.

 

[newbiecel]

Hello Eric

That worked so my problem is solved

Thanks for the support, Gibby

 

[Eric W]

Glad to help!