offset return

[CreativeRova]

Hi,

i have a table that i want to extract from. 1st 2nd 3rd etc

eg

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]team
[/TD]
[TD]average
[/TD]
[TD][/TD]
[TD]position
[/TD]
[TD]TEAM
[/TD]
[/TR]
[TR]
[TD]GIANTS
[/TD]
[TD]1.000
[/TD]
[TD][/TD]
[TD]FIRST
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TWINS
[/TD]
[TD]0.000
[/TD]
[TD][/TD]
[TD]SECOND
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]RED SOX
[/TD]
[TD]0.750
[/TD]
[TD][/TD]
[TD]THIRD
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]METS
[/TD]
[TD]0.500
[/TD]
[TD][/TD]
[TD]FOURTH
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

See above. in B5 i want it to automatically choose the highest average team and display the team name.
i.e - First will be Giants as their average is highest.
I know the formula to sort hjighest is large(b2:b5,1) and so on but that return the average not the team name how do i use the offset formula with LARGE to return the name?

 

[AhoyNC]

Try.
Assumes there are not duplicate averages.
Copy formula down as needed.

Excel Workbook
	A	B	C	D	E	F
1	team	average		position	TEAM
2	GIANTS	1		FIRST	GIANTS
3	TWINS	0		SECOND	RED SOX
4	RED SOX	0.75		THIRD	METS
5	METS	0.5		FOURTH	TWINS
6
Sheet1

 

[CreativeRova]

Hi AhoyNC,

i am getting an error #NUM! on this formula. Also there are duplicate values.

 

[MarcelBeug]

My suggestion would be to add a sort code in column C.

Suppose your data is in rows 2:30, add the following array formule in C2 (confirm with CTRL-SHIFT-ENTER, not just ENTER) and copy down:

 =1+SUM(--($B$2:$B$30>B2))+SUM(--(B$1:B1=B2))

Now enter in E2 the following formula and copy down:

 =INDEX($A$2:$A$30,MATCH(ROW(E2)-ROW(E$2)+1,$C$2:$C$30,0))

For the position (in numbers and including duplicates) you can use the following array formula in D2 (confirm with CTRL-SHIFT-ENTER, not just ENTER) and copy down:

 =1+SUM(--($B$2:$B$30>INDEX($B$2:$B$30,INDEX(E2,$A$2:$A$30,0))))

 

[MarcelBeug]

Small correction, the last code (in D2) should be:

 =1+SUM(--($B$2:$B$30>INDEX($B$2:$B$30,[B]MATCH[/B](E2,$A$2:$A$30,0))))

[/

 

[CreativeRova]

Hi MarcelBeug,

That worked perfectly. Never seen that code before and the third code for position didn't seem to work for me but that fine since it is sorting the data highest to lowest i know what positions each is in.

 

[Cosmic Wizard]

Small correction, the last code (in D2) should be:

 =1+SUM(--($B$2:$B$30>INDEX($B$2:$B$30,[B]MATCH[/B](E2,$A$2:$A$30,0))))

[/

Hi MarcelBeug, great work, really helpful.

Just two quick question, would there be any way to change the Position from numbers to say First, Second, Third etc?

Another question, would there be any way to have equal places; say two fourth, and then the position number would skip to six?

If not all good, just that may be easier in some cases, thankyou

 

[Cosmic Wizard]

Hi MarcelBeug,

That worked perfectly. Never seen that code before and the third code for position didn't seem to work for me but that fine since it is sorting the data highest to lowest i know what positions each is in.

Hi CreativeRova, it did work for me after some love.
Copy and pasting didn't work for me. First, click on D2; then in the formula box above past the formula. I then went to the front of the formula and deleted the =1+SUM, and then redid the =sum, click on the sum button which appeared, then added the 1+, and then confirm with CTRL-SHIFT-ENTER, not just ENTER.
Really good formulas to have up your sleeve.

 

[Cosmic Wizard]

Actually found a VBA code here https://www.ablebits.com/office-add...bers-words-excel/#convert-figures-words-addin which does it!