The N function and the idiosyncrasies of Excel

Wow, this is news to me: https://exceljet.net/formula/return-...index-function

Has this been known for a very long time? I recall i saw some N function swooshing by recently somewhere but didn't think anything of it. Today I took on a task of summarizing a column of numbers that was in different currencies. After some pointless wrestling with lookup and what not I learned what must be one of the weirdest tricks in Excel.

I'm not sure this causing me to love Excel more or hate Excel more...

It also makes me wonder what Excel would look like if it was written from scratch today with all the experiences gathered from this forum and all the other users worldwide. For sure it would be easier to use INDEX/MATCH in an array formula...

I wrote on that over four and a half years ago:

https://excelxor.com/2014/09/05/inde...ray-of-values/

I first saw it in a thread on this very forum, maybe 5 years ago, from a Chinese member. And Lori then traced it back to some Chinese Excel forums. Not sure how it was originally discovered, though.

Regards

Ok, interesting. Excel is a weird beast.

I came across that same exceljet link earlier this year. I think my reaction was similar to yours, Jacc. "Wow, that's so cool!" followed by "Why the hell can't it do this natively?"

Ha ha... yeah, you don't wether to laugh or cry!

To read more:

http://www.snb-vba.eu/VBA_Arrays_en.html#L_6.0

@pepe

Pas de problème: continuez sans peur ou utilizez le 'link' http sans 's'
Le site est le plus innocente possible.

Originally Posted by snb

To read more:

http://www.snb-vba.eu/VBA_Arrays_en.html#L_6.0

Nice!
I was reminded of this old thread and the impressive solution of apo: https://www.excelforum.com/excel-pro...ml#post4138397

Revisited the thread and lo and behold, he links right back to your site.

Originally Posted by XOR LX

I wrote on that over four and a half years ago:

https://excelxor.com/2014/09/05/inde...ray-of-values/

I first saw it in a thread on this very forum, maybe 5 years ago, from a Chinese member. And Lori then traced it back to some Chinese Excel forums. Not sure how it was originally discovered, though.

Regards

T function as well?!? Oh man...


And what is the difference between IF(1,.... and IF({1},.... ?

https://www.excelforum.com/excel-for...g-headers.html

And why will this not work?

Formula:

Please Login or Register  to view this content.

I'm trying to solve this: https://exceljet.net/puzzle/formula-...o-days-of-week

Actually the IF{1) could be replaced with the unary plus operator in the example in that link, i.e.:

=SUMPRODUCT(N(+'Raw Data'!$B:$B),N(+'Raw Data'!$A:$A),N('Raw Data'!$A:$A>=B2))-SUMPRODUCT(N(+'Raw Data'!$B:$B),N(+'Raw Data'!$A:$A),N('Raw Data'!$A:$A>B3))

Re {1}, I should have included that in my post. Basically, if the values form part of a range (as opposed to hard-coded values as part of an array constant) then {1} is required. For example, with B1, B2 and B3 containing 1, 4 and 8 respectively:

=SUM(INDEX(A1:A10,N(IF({1},B1:B3))))

is equivalent to:

=SUM(INDEX(A1:A10,N(IF(1,{1,4,8}))))

Regards

Originally Posted by Jacc

And why will this not work? =TEXTJOIN("",1,IF(MID($A$1,(ROW(INDIRECT("1:7"))),1)="Y",INDEX($A$6:$A$12,IF({1},N(ROW(INDIRECT("1:7")))))),"")

As in the previous examples, the N (highlighted red) goes before the IF, not before (in this case) ROW.

Regards

Jeez... that could have taken days for me to discover. Thanks!

Originally Posted by XOR LX

Actually the IF{1) could be replaced with the unary plus operator in the example in that link, i.e.:

=SUMPRODUCT(N(+'Raw Data'!$B:$B),N(+'Raw Data'!$A:$A),N('Raw Data'!$A:$A>=B2))-SUMPRODUCT(N(+'Raw Data'!$B:$B),N(+'Raw Data'!$A:$A),N('Raw Data'!$A:$A>B3))

Why is + enough in that case?