• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Get max reference number for each input value

Harish R

New Member
I have a piece of data where one user has multiple reference numbers, I want the max reference number for each user. Kindly refer to the attached file.
 

Attachments

  • Untitled.png
    Untitled.png
    3.8 KB · Views: 14
Harish R
If You had an Excel-file then You could get all of those from Pivot-table without any formula.
It's a challenge to refer that to Your attached file.
 
I think you were responding to @Bosco's post. The '14' and '6' are magic parameters required by AGGREGATE to determine which operation to perform. '14' is equivalent to LARGE and gives the kth largest value, whilst the '6' causes it to ignore errors. Bosco deliberately forced #DIV/0! errors for the items he did not want to see included in the calculation. Another useful feature of the AGGREGATE function in legacy Excel is that it acted as an array wrapper, allowing array formulas to be written without needing CSE.
 
I think you were responding to @Bosco's post. The '14' and '6' are magic parameters required by AGGREGATE to determine which operation to perform. '14' is equivalent to LARGE and gives the kth largest value, whilst the '6' causes it to ignore errors. Bosco deliberately forced #DIV/0! errors for the items he did not want to see included in the calculation. Another useful feature of the AGGREGATE function in legacy Excel is that it acted as an array wrapper, allowing array formulas to be written without needing CSE.
thanks so much.
 
Back
Top