Suggestions for LARGE when there are many results at 100%

[PWD]

Good afternoon, I'd just like to throw this out there. We have some survey results & have to produce some data summaries. These include listing the top 4 & bottom 4 answers per team. There are some 40 teams and some 80 questions. So for each team we have to list the top 4 answers (the actual question as well as the % result) based on the percentage scores. Ordinarily I would use LARGE but there are several teams that score 100% for 10 or more questions.

So I'd like suggestions on how to approach this in as simple a way as possible.

Many thanks,
D€$

 

[SkipVought]

Hi,

Top 4
[tt]
Score Occurrences
100% 10
95% 7
92% 15
90% 22
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWD]

Hi Skip. I see what you mean but what I need/want is a suggestion for choosing 4 from those 10 occurrences of 100%. As I understand it LARGE,1 to LARGE,4 will work through the scores & pull out "100%". That's fine; however, I also need to pull through the actual question associated with those answers.

I have got this far to find the actual question:- The questions are in a named range "Questions" & I'm using a small test range of questions & scores in Columns "B" to "E". These are the formulae in Row 3 to pull out the question text from Row 2:-

=INDEX(Questions, 1, MATCH(LARGE(B3:E3, 1), B3:E3, 0))
=INDEX(Questions, 1, MATCH(LARGE(B3:E3, 2), B3:E3, 0))
=INDEX(Questions, 1, MATCH(LARGE(B3:E3, 3), B3:E3, 0))
=INDEX(Questions, 1, MATCH(LARGE(B3:E3, 4), B3:E3, 0))

Trouble is, this matches with the first occurrence of 100% that it encounters each time - so I only get the wording for the first question answered at 100% each time.

Additionally(!) I can't have a totally random method of selecting 4 from n as I have to forward the results to "My People" who will probably want this run more than once & need to have confidence in the output while they tweak other aspects of the report.

If I have to (am able to!) choose the first 4 columns that contain 100% then that will be better than nothing, I guess.

What my colleague has done previously is to add a tiny increment to each answer so, for example, 100 turns into 100.00000001, 96.78 to 96.78000002, 100 to 100.00000003, etc. This has involved creating a new sheet with the answers, transposing the data to columns, adding new columns, then using RANK then doing a LOOKUP to the original results using the answering Team's code to find the question text. I was just trying to find a more efficient way.



Many thanks,
D€$

 

[PWD]

OK, I've been testing again & have a small amount of data. The questions are in "B2:E2". I have copied them into "G2:J2".

The scores start in Row 3 ("B3"). I have added these formulae:-

G3=RANK(B3,$B$3:$E$3)+COUNTIF($B3:B$3,B3)-1
H3=RANK(C3,$B$3:$E$3)+COUNTIF($B3:C$3,C3)-1
I3=RANK(D3,$B$3:$E$3)+COUNTIF($B3$3,D3)-1
J3=RANK(E3,$B$3:$E$3)+COUNTIF($B3:E$3,E3)-1

From there I can get the top ranking questions with:-

Top1st =INDEX(G2:J2, 1, MATCH(SMALL(G3:J3, 1), G3:J3, 0))
Top2nd =INDEX(G2:J2, 1, MATCH(SMALL(G3:J3, 2), G3:J3, 0))
Top3rd =INDEX(G2:J2, 1, MATCH(SMALL(G3:J3, 3), G3:J3, 0))
Top4th =INDEX(G2:J2, 1, MATCH(SMALL(G3:J3, 4), G3:J3, 0))

I can still use

=LARGE(B3:E3,1)

For the Top1st score etc.

It still feels like a lot of faffing about & it only takes the first (leftmost) 4 occurrences of “100%” but at least it feels as if I’ve achieved something!


Many thanks,
D€$

 

[macropod]

See my solution for matching in Excel with repeated data, at:

http://www.techsupportforum.com/forums/f57/excel-formula-issues-375768.html

Cheers
Paul Edstein
[MS MVP - Word]

 

[PWD]

Message

You are not logged in or you do not have permission to access this page.

I'll get myself signed up & see what's what.

Many thanks,
D€$

 

[macropod]

Hi PWD,

An alternative download source is:

http://windowssecrets.com/forums/showthread.php/125894-formula-to-return-all-matches-from-a-list

(post #3)

Cheers
Paul Edstein
[MS MVP - Word]

 

[PWD]

Hi Paul, I've got it downloaded OK now, thanx.

I think I've understood the formulae except for - heck, let's just say almost all of "A2"!! Don't get me wrong, I've read the Comments but

CELL("filename"

and "]" are new concepts to me. Help!!!

Once I get my head around what it all means, I'm pretty confident that I'll be able to adapt it to my situation.

Many thanks,
D€$

 

[PWD]

Wow, all that formula just to get the current sheet's name!! Got it.

Many thanks,
D€$