index with row function

[PaulGSmith7]

Hi all,
really trying to get my head around a little bit of code:

INDEX((year="Y07")*(gender="f")*Score+ROW(Score)*0.000001,0)

It is part of the code for matching a name with a score ranked using the large function, and somehow it helps return a unique name even with duplicate scores.
It is part of this entire line:

=IFERROR(INDEX(Q$2:Q$150,MATCH(LARGE(INDEX((year="Y07")*(gender="f")*Score+ROW(Score)*0.000001,0),ROWS(A$1:A1)),INDEX(Score+ROW(Score)*0.000001,0),0)),"")

I just can't get my head around how it works and would really appreciate it if anyone could explain it.
Many thanks in advance.

[CAntosh]

It looks to me like a formula to return ranked entries with ROW built in as a tie-breaker.

The middle part: INDEX((year="Y07")*(gender="f")*Score+ROW(Score)*0.000001,0) ...will return a range of values consisting of SCORE + a tiny decimal corresponding with the row for the cells that meet the year and gender conditions. For those that don't, it will return a zero plus the tiny decimal. LARGE will then find the biggest of those values (second largest, then third, etc. as you fill down). That return is then matched against an array of just the 'SCORE + tiny decimal' values to figure out which row/entry had the selected LARGE value. The MATCH result is then run against Q2:Q150 to return the corresponding value in Q. Hopefully that makes sense?

[PaulGSmith7]

Do you mean it adds the tiny decimal to the scores or the values include the scores and the tiny decimals. i.e. will it return say 40 and 0.000002 or 40.0000002?
What do you mean "for those that don't"?
I thought that the ,0 meant it returns values for all rows.
Are you able to give me a simplified example of what is happening?
I greatly appreciated your help.
Paul

[FlameRetired]

Do you mean it adds the tiny decimal to the scores or the values include the scores and the tiny decimals. i.e. will it return say 40 and 0.000002 or 40.0000002?
What do you mean "for those that don't"?
I thought that the ,0 meant it returns values for all rows.
Are you able to give me a simplified example of what is happening?
I greatly appreciated your help.
Paul

1. Yes it returns 40.0000002
2. Those that don't would return small numbers such as 0.0000002, 0.0000003, 0.0000007 ... etc. If you do not want to risk matching those small numbers an IF formula to replace the INDEX((year="Y07")*(gender="f")*Score+ROW(Score)*0.000001,0) part might be the remedy. You would likely have to array enter it then.
3. RE: the simplified example. Please upload a small Excel workbook that best describes what you are working with ... please no pictures or screen shots. That way we all have something real in our hands to reference.

To attach a file to your post,

• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”
• be sure to desensitize the data

The file name will appear at the bottom of your reply.

[PaulGSmith7]

Hi,
I've attached the said worksheet.
I have been using pivot tables to extract the data but am changing to do without.
Scores are whole numbers so the small numbers won't get matched.
I would so love to get my head around this statement. I need to see what is happening with real data and I think a simplified example would really help.
Thanks again for taking the time to help - it is greatly appreciated.
Paul

[CAntosh]

FlameRetired's explanation is spot on. I'll try to elaborate using the first half dozen or so rows of actual data in your sample.

Within your formula, you're creating arrays. If we look at the formula that should be in E19:

=INDEX(name,MATCH(LARGE(INDEX((year="Y08")*(gender="m")*Score+ROW(Score)*0.000001,0),ROW(A1)),INDEX(Score+ROW(Score)*0.000001,0),0))

Start with the middle: INDEX((year="Y08")*(gender="m")*Score+ROW(Score)*0.000001,0)

(year="Y08") returns an array of zeroes and ones for every value in the 'Year' column. A one (for true) if it's Y08, a zero if it's anything else. The year values starting in T2 are {Y10,Y10,Y08,Y08,Y08,Y10,Y09, Y08, etc.), so the start of the formula returns an array that looks like this: {0,0,1,1,1,0,0,1,1,0,etc.}; it'll only show ones for Y08 entries.. The same process occurs with the 'gender = m' part of the formula, returning an array of {1,1,0,1,1,1,1,1,1,1,1,0,0,0, etc.} showing only the male entries. These two arrays are multiplied together, so (year="Y08")*(gender="m") leaves us with a new array that only returns ones when both clauses are met, so: {0,0,0,1,1,0,0,1,1,0,etc.}.

This array of ones and zeroes is then multiplied by score, so zero times score leaves a zero, however entries with ones will return the score from those rows, creating a new array that looks like this:
{0,0,0,98,53,0,0,25,58,etc.}

ROW*.000001 produces a similarly sized array of the tiny decimals corresponding to the row, as described by FlameRetired. It is then added to the previous array, resulting in an array that looks like this: {0.0000002,0.0000003,0.0000004,98.0000005,53.0000006,0.0000007,0.0000008,25.0000009,58.0000010,etc.}

Thus INDEX((year="Y08")*(gender="m")*Score+ROW(Score)*0.000001,0) reduces the scores that don't match the criteria down to negligible decimals while eliminating all ties and leaving the scores that DO match roughly regular sized. The formula then uses "LARGE" on this array to take the first largest, then second, third, etc. as you fill down:

ROW(A1) returns a one in E19. It becomes ROW(A2) in E20, which returns a 2... Row(A3) in E21, returning a 3, etc. When paired with large, it returns the first largest when in E19, 2nd largest in E20, etc., so:

LARGE(INDEX((year="Y08")*(gender="m")*Score+ROW(Score)*0.000001,0),ROW(A1)) returns the highest score in that big array created by the INDEX portion, which would be the 99.0000028 generated by the data in W28. The second largest in E20 would return the 98.0000005 visible in the sample considered above. The MATCH part of the equation then matches this value against an array of just the scores and values - which would look like this {43.0000002,63.0000003,30.0000004,98.0000005,53.0000006, etc.} - to figure out WHERE in the range the match occurs. The largest match (99.0000028) would return a 27, since it's the 27th entry. For E20, the second largest would return a 4, since it's the fourth entry.

INDEX then applies the resulting MATCH number to the range with names to return the 27th name in E19, the 4th name in E20, etc.

I hope that makes sense? If you've never used it, the "Evaluate Formula" button on the "Formulas" tab of your ribbon will walk you through a selected formula cell step by step. It might be useful for a full-sized version of what I've attempted here.

[PaulGSmith7]

The penny has dropped.
Thank you so much.
Kind regards

[PaulGSmith7]

One more question.
How can I eliminate zeros from:

=SMALL(INDEX((Table1[100m])*(year="y07")*(gender="m"),),ROW($A$1))

please?

[AliGW]

Where and when do they occur, these zeroes?

[PaulGSmith7]

Thanks for reply.
Someone sent in:
=SMALL(if(INDEX((Table1[100m])*(year="y07")*(gender="m"),)>0,INDEX((Table1[100m])*(year="y07")*(gender="m"),)),ROW($A$1))
and it works.
Just wish it could be done without an array formula.
Thanks
Paul