troubleshooting Question

How does this work? SMALL((A6#,C10:C18),SEQUENCE(ROWS(A6#)+ROWS(C10:C18)))

Avatar of Anthony Mellor
Anthony MellorFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft ExcelMicrosoft Office
4 Comments1 Solution16 ViewsLast Modified:
SMALL((A6#,C10:C18),SEQUENCE(ROWS(A6#)+ROWS(C10:C18)))
Where:
=SMALL(array, k)
I mean: the help syntax bears barely any relation with the solution above.

SHORT VERSION: Why does that work where UNIQUE() does not?


School's in.

So far I understand that small() picks only one example of each item in the list.

I can see that (A6#,C10:C18) concatenates two arrays, not just one, but it creates one out of two. I can also see that when I get my Value! error it is because I can reproduce the error thus:
Where B6:B7 are in Sheet1!
=B6:B7:Sheet2!B9:B10.        ==Value!
and also
=B6:B7:Sheet1!J6:J7            == Value! So even though in the same sheet, the Sheet1! reference generates the error.
=B6:B7:J6:J7                        ==0 No error.
So we just have to know this.

k is represented by: 
SEQUENCE(ROWS(A6#)+ROWS(C10:C18))
where:
=SEQUENCE(rows,[columns],[start],[step])
In this case only the rows argument is used.
This generates a series of numbers starting from 1 and running to the number of rows (and thus items) in the concatenated range A6#and C10:C18 (both same/current sheet) (51 in thee example)

So k is not one digit.. but it is; it is 51.

And the penny drops!! kinda.

Where we have k being the same as the number of items in range, SMALL() is looking for .. what?
Help seems confusing because it says k returns the k-th smallest value. That is ONE VALUE only.
Then it says "...return values...." which is what is being done in the above. So SMALL is returning not just one value, but every value smaller or equal to  the largest value in the range, and it returns only one example of each, no point in returning duplicates as that is not answering the question.

How is that different from UNIQUE() ?

Example: LDATELISTSCOMBINE_SchoolEE.xlsx

This is like being on one of those never ending treadmills.. exercise for my TV watching brain.

Anthony












Notes for self:

Description

Returns the k-th smallest value in a data set.
Use this function to return values with a particular relative standing in a data set.

Syntax

SMALL(array, k)
The SMALL function syntax has the following arguments:
  • Array    Required. An array or range of numerical data for which you want to determine the k-th smallest value.
  • K    Required. The position (from the smallest) in the array or range of data to return.


ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros