Can someone please explain this set of functions?

[tigerbanana]

Hi all,

Below is the functions I used in a lucky draw thing and it works but I don't really get how the middle part works
Can somebody please explain the aggregation part of it? and how does the range works in the function Match also.

=IF(ROWS(C$2:C2)>B$2,"",INDEX(A$2:A$6,AGGREGATE(15,6,((ROW(A$2:A$6)-ROW(A$2)+1)/ISNA(MATCH(A$2:A$6,C$1:C1,0))),RANDBETWEEN(1,ROWS(A$2:A$6)-COUNTA(C$1:C1)+1))))

Just need 3 columns for this function.
Column A is the draw list.
Column B has only one cell which is the number of draws
Column C is where to place the above function and can simply drag down to the number you need and can be refresh by pressing F9.

When I try to figure out how it works, I found out that some of the middle parts will get errors if they just stand alone.
Thank you in advance for your time and effort!

Regards,
Leo

[FlameRetired]

Hi Leo. Welcome to the forum. It helps ... we're volunteers ... if you upload a small representative Excel workbook. Otherwise we have to "invent" the data and try and explain from there.

To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),

• be sure to desensitize the data
• 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”

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

[tigerbanana]

Noted and thank you very much!

[JeteMc]

Use the Evaluate Formula feature to see how the formula works.
As for the AGGREGATE function, when you look at the help, there are 19 functions that can be combined with 8 options, therefore the functions/options are aggregated. In this case AGGREGATE is using function 15 (small) along with option 6 (ignore errors) to randomly return the Kth smallest row of the numbers in column A not already in use in column C.
As for the MATCH function, it is matching the numbers that have previously been used in the cells in column C to the numbers listed in column A.
Let us know if you have any questions.