Average vlookup or a range, then calculation

[timetabler]

I am trying to calculate an average points score from a values in a list:
Every student is given a grade which corresponds to a points value (shown on right). There are 250 students on the list, only showing "Fred Smith" for clarity.

I want to calculate the average points, BUT each point is weighted, so in the example below U4 is weighted as "3", but the other units are weighted "1".


I have typed the example of the values I would like it calculate.
All ideas appreciated.


Sheet1

	A	B	C	D	E	F	G	H	I	J	K
Fred Smith	5b
...

<COLGROUP> <COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 74px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"> </COLGROUP><TBODY>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #ffcc00, colspan: 6, align: center"]Technology[/TD]
[TD="bgcolor: #c0c0c0, align: center"]Average[/TD]

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

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="bgcolor: #ffcc00"]Weighting[/TD]
[TD="bgcolor: #ffcc00, align: center"] 1.00 [/TD]
[TD="bgcolor: #ffcc00, align: center"] 1.00 [/TD]
[TD="bgcolor: #ffcc00, align: center"] 1.00 [/TD]
[TD="bgcolor: #ffcc00, align: center"] 3.00 [/TD]
[TD="bgcolor: #ffcc00, align: center"] 1.00 [/TD]
[TD="bgcolor: #ffcc00, align: center"] 1.00 [/TD]

[TD="align: center"]1b[/TD]
[TD="align: center"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="bgcolor: #ffff00"]Student[/TD]
[TD="bgcolor: #ffff00"]U1[/TD]
[TD="bgcolor: #ffff00"]U2[/TD]
[TD="bgcolor: #ffff00"]U3[/TD]
[TD="bgcolor: #ffff00"]U4[/TD]
[TD="bgcolor: #ffff00"]U5[/TD]
[TD="bgcolor: #ffff00"]U6[/TD]
[TD="bgcolor: #c0c0c0"] [/TD]

[TD="align: center"]1a[/TD]
[TD="align: center"]11[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]3a[/TD]
[TD="align: right"]4a[/TD]

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

[TD="align: right"]2a[/TD]
[TD="bgcolor: #c0c0c0"] [/TD]

[TD="align: center"]2c[/TD]
[TD="align: center"]13[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: center"]2b[/TD]
[TD="align: center"]15[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]21[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]29[/TD]

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

[TD="align: center"]2a[/TD]
[TD="align: center"]17[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

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

[TD="bgcolor: #c0c0c0, align: center"]4b[/TD]

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

[TD="bgcolor: #cacaca, align: center"]8[/TD]

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

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

[TD="bgcolor: #cacaca, align: center"]9[/TD]

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

[TD="bgcolor: #cacaca, align: center"]10[/TD]

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

[TD="bgcolor: #cacaca, align: center"]11[/TD]

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

[TD="bgcolor: #cacaca, align: center"]12[/TD]

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

[TD="bgcolor: #cacaca, align: center"]13[/TD]

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

[TD="bgcolor: #cacaca, align: center"]14[/TD]

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

[TD="bgcolor: #cacaca, align: center"]15[/TD]

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

</TBODY>

Spreadsheet Formulas

Cell Formula H6 =AVERAGE(B6:G8) <TBODY> </TBODY>

<TBODY>
</TBODY>

Excel tables to the web >> Excel Jeanie HTML 4

 

[ca.moazzam]

Hi please give a bit of more explanation and mention the result that you require in this example.

Moazzam

 

[timetabler]

Hi Mozzam, I have tried to give an example in Rows 6-9 gives an example of the numbers I would like to average, so that when looking at rows 2 and 4 together, I would have 3a x1, 4a x 1, 5b x 1, 4a x 3 and 2a x 1. using the lookup table this would give the following values: 21, 29, 33, 29, 29, 29, 17 which average 26.714, I would then use this to back calculate a value of "4b".

Hope that helps.

 

[Snakehips]

tmetabler,

For starters, here is a formula for the weighted average.

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K
1	Technology							Average	Weighted	1c	7
2	Weighting	1	1	1	3	1	1	Grade	Average	1b	9
3	Student	U1	U2	U3	U4	U5	U6			1a	11
4	Fred Smith	3b	4a	5b	4a		2a		26.71428571	2c	13
5										2b	15
6										2a	17
7										3c	19
8										3b	21
9										3a	23
10										4c	25
11										4b	27
12										4a	29
13										5c	31
14										5b	33
15										5a	35
Sheet1

Cell Formulas
Range	Formula
I4	=(IFERROR(VLOOKUP(B4,$J$1:$K$15,2,FALSE)*B2*B2,0)+IFERROR(VLOOKUP(C4,$J$1:$K$15,2,FALSE)*C2,0)+IFERROR(VLOOKUP(D4,$J$1:$K$15,2,FALSE)*D2,0)+IFERROR(VLOOKUP(E4,$J$1:$K$15,2,FALSE)*E2,0)+IFERROR(VLOOKUP(F4,$J$1:$K$15,2,FALSE)*F2,0)+IFERROR(VLOOKUP(G4,$J$1:$K$15,2,FALSE)*G2,0))/SUMPRODUCT($B$2:$G$2*(B4:G4>""))

There may be a simpler looking solution but at the moment I don't see it.

Hope that helps.

 

[Snakehips]

timetabler,


Here is revised formula for weighted avereage. I had omitted making a couple of cell references absolute.

The below also includes an ARRAY formula that will get the grade.

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K
1	Technology							Average	Weighted	1c	7
2	Weighting	1	1	1	3	1	1	Grade	Average	1b	9
3	Student	U1	U2	U3	U4	U5	U6			1a	11
4	Fred Smith	3b	4a	5b	4a		2a	4b	26.71428571	2c	13
5										2b	15
6										2a	17
7										3c	19
8										3b	21
9										3a	23
10										4c	25
11										4b	27
12										4a	29
13										5c	31
14										5b	33
15										5a	35
Sheet1

Cell Formulas
Range	Formula
I4	=(IFERROR(VLOOKUP(B4,$J$1:$K$15,2,FALSE)*$B$2,0)+IFERROR(VLOOKUP(C4,$J$1:$K$15,2,FALSE)*$C$2,0)+IFERROR(VLOOKUP(D4,$J$1:$K$15,2,FALSE)*$D$2,0)+IFERROR(VLOOKUP(E4,$J$1:$K$15,2,FALSE)*$E$2,0)+IFERROR(VLOOKUP(F4,$J$1:$K$15,2,FALSE)*$F$2,0)+IFERROR(VLOOKUP(G4,$J$1:$K$15,2,FALSE)*$G$2,0))/SUMPRODUCT($B$2:$G$2*(B4:G4>""))
H4	{=IFERROR(INDEX($J$1:J15,MATCH(I4,$K$1:$K$15,0),0),INDEX($J$1:$J$15,MATCH(1,--(($K$1:$K$15-I4)>0.0001),0),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

I hope that sorts it for you.

 

[ca.moazzam]

Hi can anybody tell me how do u guys copy images here? When I try to copy/ paste from excel I find paste option of IE disabled.

 

[Snakehips]

ca.moazzam,

Go to any question page and you will see two 'Sticky' posts at the top. Click on the first one ,Recommended Add-ins and Links.

Under Recomended Add-ins you will find links to Excel Jeanie and MrExcel HTMl Maker. Both are easy to download and easy to use.

I use both, MrXL is the simplest but unless I'm missing a trick it doesn't post conditional formatting formulas whereas Excel Jeanie does.

Using these tools to display a snapsot of your worksheet makes both asking and answering questions so much clearer.

'A picture paints a thousand words'....... well maybe not a thousand but lots.

Hope that helps. Have fun.

 

[ca.moazzam]

Thanks a million Snakehips. I asked this question a few times but nobody bothered to answer.