Lookup cell and n cells below - return the average

[merchant60b]

Hi

I am looking for a way to return the average of multiple cells using a vlookup.

This is in respect of sporting prize money. Where a competitor finishes 1st, they get a set prize in $ and I have set the Vlookup to return this figure where I input 1st as their position.

It gets complicated where more than one competitor tie. For example, where 4 players tie for 15th then the prize for each competitor is the average of the prizes for 15th, 16th, 17th and 18th.

If I was to add a column for the number of people joint in said position, is there a way to return the average of those prizes? i.e. vlookup 15th and the n cells below and return the average, where "n" is a figure that I input into a different cell.

Hoping my table will be as follows:

Competitor/Position/Number joint with/Prize

and the prize table, where the vlookup is taken from being:

Position/Prize

Thanks

 

[Aladin Akyurek]

Try to post a small sample (not an image of any kind) along with the expected result for that sample.

 

[merchant60b]

No problem.

Position - Prize

1 - $100,000
...
15 - $10,000
16 - $9,000
17 - $8,000
18 - $7,000
19 - $6,000
20 - $5,000

So the table will read

Competitor - Position - No. Joint - Prize

Competitor A - 1 - 0 - $100,000
Competitor B - 15 - 3 - $8,500 (being the average of positions 15,16,17,18)

 

[Nishant94]

Book1
	A	B	C	D
1	Position	prize
2	1	100000
3	2	90000
4	3	80000
5	4	70000
6	5	60000
7	15	10000
8	16	9000
9	17	8000
10	18	7000
11	19	6000
12	20	5000
13
14
15	Competitor	Position	No. Joint	Prize
16
17	Competitor A	1	0	100000
18	Competitor B	15	3	8500
19	Competitor B	2	2	80000
20
Sheet8

Cell Formulas
Range	Formula
D17	=AVERAGE(INDEX($B$2:$B$12,MATCH(B17,$A$2:$A$12,0)):INDEX($B$2:$B$12,MATCH(B17,$A$2:$A$12,0)+C17))

 

[merchant60b]

	A	B	C	D
Position	prize
Competitor	Position	No. Joint	Prize
Competitor A
Competitor B
Competitor B

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

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

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

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

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]80000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]70000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]60000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]9000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]8000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]7000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]6000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

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

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

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

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

[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]100000[/TD]

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

[TD="align: right"]15[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]8500[/TD]

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

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

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

</tbody>

Sheet8​

[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] "]D17[/TH]
[TD="align: left"]=AVERAGE(INDEX($B$2:$B$12,MATCH(B17,$A$2:$A$12,0)):INDEX($B$2:$B$12,MATCH(B17,$A$2:$A$12,0)+C17))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Brilliant, thanks v much