Anthony Mellor
asked on
How does this work? SMALL((A6#,C10:C18),SEQUENCE(ROWS(A6#)+ROWS(C10:C18)))
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Anthony,
Hoist a mug and say "Cheers!" whenever the Union trick works.
Obviously, a better solution is needed when two arrays need to be joined...
Brad
Hoist a mug and say "Cheers!" whenever the Union trick works.
Obviously, a better solution is needed when two arrays need to be joined...
Brad
ASKER
Shame SQL isn't built in to the functions list; so close in MS Query.
Anthony
Anthony
ASKER
that sounds like it explains why I get differing results when trying the union with different functions, which seems to have neither rhyme nor reason.
Anthony