Excel 2010 LARGE followed by HLOOKUP - is there a better way? 1

[PWD]

Good afternoon, I've inherited a task that involves searching for the largest 4 values per row in a table of answers then listing the scores, together with the actual questions. At present it appears to be done by finding LARGE 1,2,3 & 4, then doing an HLOOKUP for each of those values, after copying the actual question texts to the bottom row of the table. Is there some way that this could be simplified? Some sort of OFFSET, incremented by +1 for each subsequent row?

The answers are formatted to 8 decimal places so we're pretty confident that we're getting the right results; although I'm not sure how we're factoring in more than one 100% result!!

Any assistance with my problem would be gratefully received.

Many thanks,
D€$

 

[mintjulep]

It's not entirely clear what you are trying to do.

But have a look at the RANK() function, entered as an array function.

 

[PWD]

Hi mintjulep. Basically we have a sheet of data with the answers to around 80 questions from around 45 teams.

The teams are in Column A & the answers from Column B to Column whatever

The question texts start in Row 2 and the scores start in Row 3.

"My people" want to compile a report that shows the 4 highest & 4 lowest scoring answers, per team. So my thought had been to use LARGE & SMALL at the end of each row of data. But then I was stumped as how I'd get the related question text - sitting in Row 2.

In the meantime my colleague has dug up a 'ranking' sheet that we've used on previous projects & I think that that will have produced what is required. I'm waiting feedback now.

Many thanks for your interest.

Many thanks,
D€$

 

[lionelhill]

In case it helps:

For a spreadsheet with team-names in column A, scores in columns B to D, and question texts in row 2, the following in cell E3 will display the question text associated with the 2nd largest score in row 3. If you copy this down the column, it will do the same for each row:

=INDEX(Questions, 1, MATCH(LARGE(B33, 2), B33, 0))

"Questions" is the name I've given to the range B22 where the question texts can be found. Obviously you can add columns to choose the greatest, 3rd-greatest, worst etc. scores.

Explanation: LARGE will give you the appropriate largest value.
Instead of using this with HLOOKUP, you can use MATCH to return its position in that row. This corresponds to the position of the question in row 2.
You can use INDEX to retrieve the nth question from row 2.
This is almost identical to the LARGE/HLOOKUP approach, except that the questions can be anywhere you want, and don't need to be in a fixed location relative to the data you're looking-up, so it's a bit more flexible.

 

[SkipVought]

The teams are in Column A & the answers from Column B to Column whatever

The question texts start in Row 2 and the scores start in Row 3.

These two statements raise a [highlight RED][white]RED FLAG[/white][/highlight] because it seems that your structure is not conducive to Excel's analysis features.

Please post a sample of what your sheet looks like

Skip,

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

 

[PWD]

Hi Skip, this is the sort of thing I'm working with:-

1 2 3 4
Team / Question How do you..? What did you… ? Who was….? Did they…? Top1 Score #1 Top2 Score #2 Top3 Score #3
Team 1 65.38461539 56.25 89.28571429 71.42857143 Who was….? 89.28571429 Did they…? 71.42857143 How do you..? 65.38461539
Team 2 72.22222222 86.84210526 73.61111111 96.05263158 Did they…? 96.05263158 What did you… ? 86.84210526 Who was….? 73.61111111
Team 3 85.71428571 50 78.57142857 68.75 How do you..? 85.71428571 Who was….? 78.57142857 Did they…? 68.75
Team 4 70 90.38461539 65 54.54545455 What did you… ? 90.38461539 How do you..? 70 Who was….? 65
Team 5 91.50943396 68.51851852 77.27272727 61.17021277 How do you..? 91.50943396 Who was….? 77.27272727 What did you… ? 68.51851852

Wasn't sure the best way to post this - please let me know & I'll correct it.

I've now put Lionel's formula in Columns F, H & J and then used LARGE for Columns G, I & K.

I just didn't have a clue as to the syntax.

Many thanks,
D€$

 

[PWD]

Shall I post this question as a new thread?

Now I've got what I was seeking (not saying it was the best approach), is there any advice on how to select a team's top 4 answer results when, say, 17 of the questions from one team have been answered at 100%?

Many thanks,
D€$

 

[SkipVought]

Your data is impossible to parse.

And judging from the structure of the headings, will be difficult to analyse.

Yes, post new questions in new thread.

Skip,

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