The k of function {1,2,3,4,5,6} Can be expressed through a formula,
- Thread starter bines53
- Start date
Asheesh
Excel Ninja
Yes...the k part of it can be dealt with a formula..
eg ..Sum(LARGE(range,{1,2,3,4,5,6})) is as good as SUM(LARGE(range,ROW(A1:A6)))
If you try this..and you willl notice that both will yield you the same result
https://exceljet.net/formula/sum-top-n-values
eg ..Sum(LARGE(range,{1,2,3,4,5,6})) is as good as SUM(LARGE(range,ROW(A1:A6)))
If you try this..and you willl notice that both will yield you the same result
https://exceljet.net/formula/sum-top-n-values
XOR LX
Active Member
Actually, technically:
ROW(A1:A6)
returns:
{1;2;3;4;5;6}
not:
{1,2,3,4,5,6}
though for the purpose of this exercise this is unlikely to be of relevance.
However, in general, ROW(A1:A6) is not a very rigorous choice, since any row insertions within the range will most likely lead to errors here.
Better is:
ROW(INDEX(A:A,1):INDEX(A:A,6))
which gives:
{1;2;3;4;5;6}
or, if vector-type is a concern:
COLUMN(INDEX(1:1,1):INDEX(1:1,6))
which gives:
{1,2,3,4,5,6}
both of which are immune to row/column insertions within the worksheet (though the latter cannot create arrays containing more than 16,384 entries - for that it would be necessary to employ ROW and then transpose the resulting array).
Regards
ROW(A1:A6)
returns:
{1;2;3;4;5;6}
not:
{1,2,3,4,5,6}
though for the purpose of this exercise this is unlikely to be of relevance.
However, in general, ROW(A1:A6) is not a very rigorous choice, since any row insertions within the range will most likely lead to errors here.
Better is:
ROW(INDEX(A:A,1):INDEX(A:A,6))
which gives:
{1;2;3;4;5;6}
or, if vector-type is a concern:
COLUMN(INDEX(1:1,1):INDEX(1:1,6))
which gives:
{1,2,3,4,5,6}
both of which are immune to row/column insertions within the worksheet (though the latter cannot create arrays containing more than 16,384 entries - for that it would be necessary to employ ROW and then transpose the resulting array).
Regards
Hi XOR LX,
If I take the formula,
SUM(LARGE(A2:A100,COLUMN(INDEX(1:1,1):INDEX(1:1,6))))
It became array {},there is a way to avoid this case from array ?
However, it is also possible that,
SUMPRODUCT(--(LARGE(A2:A100,COLUMN(INDEX(1:1,1):INDEX(1:1,6))))).
Regards
David
If I take the formula,
SUM(LARGE(A2:A100,COLUMN(INDEX(1:1,1):INDEX(1:1,6))))
It became array {},there is a way to avoid this case from array ?
However, it is also possible that,
SUMPRODUCT(--(LARGE(A2:A100,COLUMN(INDEX(1:1,1):INDEX(1:1,6))))).
Regards
David
Last edited:
@XOR LX. Excellent response to the original question and great blog posts too. A couple of thoughts occurred to me after reading this thread:
1. If using a full row or column reference, it could be a good idea to set that reference to intersect with the cell itself - so if the cell was D5 you could set either 5:5 or D:D as the reference. That way there's no danger of accidentally causing a #REF error in the formula when you delete a row or column, since the formula itself would be deleted too.
2. It's possible to return a completely non-volatile dynamic array (via the by now familiar MODE.MULT):
=MODE.MULT(IF(COLUMN(1:1)<={1;1}*6,COLUMN(1:1)))
although clearly this is pretty inefficient unless the range is sufficiently large.
1. If using a full row or column reference, it could be a good idea to set that reference to intersect with the cell itself - so if the cell was D5 you could set either 5:5 or D:D as the reference. That way there's no danger of accidentally causing a #REF error in the formula when you delete a row or column, since the formula itself would be deleted too.
2. It's possible to return a completely non-volatile dynamic array (via the by now familiar MODE.MULT):
=MODE.MULT(IF(COLUMN(1:1)<={1;1}*6,COLUMN(1:1)))
although clearly this is pretty inefficient unless the range is sufficiently large.
XOR LX
Active Member
@Lori
1. Excellent point, and agreed.
2. Simply fantastic! I wasn't sure you could top your TREND approach, but you most certainly have.
At first glance, it doesn't appear to be a straightforward task deciding which of the efficient yet "volatile-at-workbook-open-only" INDEX approach or this slightly more resource-heavy yet fully non-volatile one is to be preferred.
Still, such considerations are almost a moot point in the light of such wonderful creativity. Great stuff.
Cheers
1. Excellent point, and agreed.
2. Simply fantastic! I wasn't sure you could top your TREND approach, but you most certainly have.
At first glance, it doesn't appear to be a straightforward task deciding which of the efficient yet "volatile-at-workbook-open-only" INDEX approach or this slightly more resource-heavy yet fully non-volatile one is to be preferred.
Still, such considerations are almost a moot point in the light of such wonderful creativity. Great stuff.
Cheers
@XOR LX, i'd actually use your approach here too.
The MMULT suggestion can make sense where the numbers lie within a limited range. I remember using within a name definition in a rather technical link(https://newtonexcelbach.wordpress.com/2011/02/04/fitting-high-order-polynomials/#comment-6047). From seeing your work, I'm sure you can come up with other clearer examples than that.
And i think the TREND alternative you refer to works better for 2D arrays,
eg: TREND(MUNIT(16)*0+171,,,0) returns all numbers from 1 to 256. (See also http://forum.chandoo.org/threads/create-an-array-of-numbers.13433/.)
It would make things so much easier if we had a function like =NUMBERARRAY(6) for this but also less interesting!
The MMULT suggestion can make sense where the numbers lie within a limited range. I remember using within a name definition in a rather technical link(https://newtonexcelbach.wordpress.com/2011/02/04/fitting-high-order-polynomials/#comment-6047). From seeing your work, I'm sure you can come up with other clearer examples than that.
And i think the TREND alternative you refer to works better for 2D arrays,
eg: TREND(MUNIT(16)*0+171,,,0) returns all numbers from 1 to 256. (See also http://forum.chandoo.org/threads/create-an-array-of-numbers.13433/.)
It would make things so much easier if we had a function like =NUMBERARRAY(6) for this but also less interesting!
XOR LX
Active Member
@Lori
Currently I'm more interested in the potential uses for MODE.MULT that you've unearthed.
For example, to generate a reduced array comprising unique numbers from A1:A10, there's quite a difference in terms of conciseness (and efficiency) between e.g.:
SMALL(IF(FREQUENCY(A1:A10,A1:A10),A1:A10),ROW(INDEX(A:A,1):INDEX(A:A,COUNT(1/FREQUENCY(A1:A10,A1:A10)))))
and:
MODE.MULT(IF({1,1},IF(FREQUENCY(A1:A10,A1:A10),A1:A10)))
It's also more than likely that a construction involving the first of these will require array-entry, whereas one involving the second may not.
Of course, this presumes that the order of elements in the reduced array is not important. In fact, sometimes we may be in a situation where we actually want the order to remain unchanged in the reduced array, a result which, using the SMALL construction, would require even further manipulation (with INDEX).
And I imagine this is only scratching the surface of what this function may have to offer. As such, you are to be commended for spearheading its belated emergence onto the wider Excel scene!
Regards
Currently I'm more interested in the potential uses for MODE.MULT that you've unearthed.
For example, to generate a reduced array comprising unique numbers from A1:A10, there's quite a difference in terms of conciseness (and efficiency) between e.g.:
SMALL(IF(FREQUENCY(A1:A10,A1:A10),A1:A10),ROW(INDEX(A:A,1):INDEX(A:A,COUNT(1/FREQUENCY(A1:A10,A1:A10)))))
and:
MODE.MULT(IF({1,1},IF(FREQUENCY(A1:A10,A1:A10),A1:A10)))
It's also more than likely that a construction involving the first of these will require array-entry, whereas one involving the second may not.
Of course, this presumes that the order of elements in the reduced array is not important. In fact, sometimes we may be in a situation where we actually want the order to remain unchanged in the reduced array, a result which, using the SMALL construction, would require even further manipulation (with INDEX).
And I imagine this is only scratching the surface of what this function may have to offer. As such, you are to be commended for spearheading its belated emergence onto the wider Excel scene!
Regards
Thanks! I think MODE.MULT hasn't caught on yet in general formula usage because it's a fairly new function (or else people confuse it with MMULT like me!)
Sajan described it intuitively on this forum as "filtering out stuff" - where the filtering out can be based on row numbers, duplicate values or any other criteria one can think of. In addition it allows one to return a column of values from a 2D array - the main advantage over other approaches being that it's not volatile.
I'm hopeful to see what other tricks you can come up with!
Sajan described it intuitively on this forum as "filtering out stuff" - where the filtering out can be based on row numbers, duplicate values or any other criteria one can think of. In addition it allows one to return a column of values from a 2D array - the main advantage over other approaches being that it's not volatile.
I'm hopeful to see what other tricks you can come up with!
Last edited: