Lookup cell and n cells below - return the average

merchant60b

New Member
Joined
Jul 23, 2018
Messages
5
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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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)
 
Upvote 0

Book1
ABCD
1Positionprize
21100000
3290000
4380000
5470000
6560000
71510000
8169000
9178000
10187000
11196000
12205000
13
14
15CompetitorPositionNo. JointPrize
16
17Competitor A10100000
18Competitor B1538500
19Competitor B2280000
20
Sheet8
Cell Formulas
RangeFormula
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))
 
Upvote 0
ABCD
Positionprize
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
 
Upvote 0

Forum statistics

Threads
1,224,558
Messages
6,179,512
Members
452,920
Latest member
jaspers

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top