# Populate a descending list of values with the corresponding name

1. ## Populate a descending list of values with the corresponding name

I am trying to populate a descending list of values with the corresponding person's name, but I keep coming up with an error with the people's names that I can't seem to figure out. I have attached the test sheet with the formulas (see column A, D, and G) and if someone could point out where my mistake is, I would appreciate it.

Thanks!

2. ## Re: Array Formula Help Needed

Question:

Can you explain why A5 is Person 16? Should A5 be the person based off of the B5 value?

Should A5 return Person 19 and A6 return Person 3 based on B5 and B6?

3. ## Re: Populate a descending list of values with the corresponding name

All formulae changed. Name:

=IFERROR(INDEX('Rankings - Data'!\$A:\$A,SMALL(IF('Rankings - Data'!\$C\$2:\$C\$21=Rankings!B5,ROW('Rankings - Data'!\$C\$2:\$C\$21)),COUNTIF(Rankings!\$B5:B5,Rankings!B5))),"")

Amount:
=IFERROR(1/(1/LARGE(INDEX(('Rankings - Data'!\$B\$2:\$B\$21=\$I\$1)*('Rankings - Data'!\$C\$2:\$C\$21),0),ROWS(B\$5:B5))),"")

The first one is an array formula. Have you upgraded to anything more recent than 2007? If so, a non-array solution is also easy.

4. ## Re: Populate a descending list of values with the corresponding name

oops. Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

5. ## Re: Populate a descending list of values with the corresponding name

I see you have marked this as solved, but did not think to comment.

You're welcome.

It'd be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

6. ## Re: Populate a descending list of values with the corresponding name

Sorry about that, thanks for your help, greatly appreciated and I learned a new formula thanks to you.

7. ## Re: Populate a descending list of values with the corresponding name

Hi Glenn,

Not sure what I'm missing, but in this attachment, why won't the formula in column N give all the same results as column A. One row, row 7 returns void, but not sure why.

8. ## Re: Populate a descending list of values with the corresponding name

bdoguc: There is a significant error in my formula:

=IFERROR(INDEX('Rankings - Data'!\$A:\$A,SMALL(IF('Rankings - Data'!\$C\$2:\$C\$21=Rankings!B5,ROW('Rankings - Data'!\$C\$2:\$C\$21)),COUNTIF(Rankings!\$B\$5:B5,Rankings!B5))),"")

Jeffrey... this one of yours is odd!!!! It certainly won't work relaibly if there are ties... The missing value has me scratching my head...

9. ## Re: Populate a descending list of values with the corresponding name

This is odd... raised to the power of extreme oddness.

Replace B7 with int(b7) or ROUND(B7,0) and it works. Remove them and it's an error again. Overtype the formula with 214 and it works.

Increase the number of dps and 214 remains 214. =EXACT(B7,'Rankings - Data'!C4) returns TRUE.

It MUST be floating point arithmetic at work as subtract them like this:

=b7-'Rankings - Data'!C4 and you get 0.000000000000000 However, like this: =(B7-'Rankings - Data'!C4)
you get: 0.000000000000028421709430404

10. ## Re: Populate a descending list of values with the corresponding name

Indeed weird. I did everything you did, and yes, it works, especially when overtyping the number 214.

11. ## Re: Populate a descending list of values with the corresponding name

It's time for some frothy suds over here. I'll let this one ferment for a while and see what occurs to me...

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1