tricks of gurus

[nbrcrunch]

When I first learned about SUMPRODUCT() I was as excited as I was when I learned to write my first SUM() formula.

So I'm reading through Excel Help about SUMPRODUCT and doesn't even begin to hint about the power of that function. This starts me wondering what other hidden gems are out there (regarding intrinsic functions). For example, the help on FACT() says that FACT(5) is equal to 1*2*3*4*5. Big deal. Is that all there is?

What other expanded functionality of built-in functions have you gurus (and lesser stars) out there discovered that you are willing to share with us?

 

[Tom Urtis]

This is the kind of question that can lead to a thread of very interesting posts. There are a lot of undocumented and underdocumented features in Excel. It would be neat to collect a few, as there is no definitive list for this kind of stuff.

Here's a start. Look into the N feature of formulas, which is largely undocumented, but which allows you to place a note in a formula to tell you something about it. Example of N:

On Sheet1, cell A2 you have the number 50 because in A1 you have the value "Price of widgets"
On Sheet2, cell B2 you have the number 125 because in B1 you have the value "Sales of widgets"
Needing to multiply them, thus:
On Sheet3, cell C1 you have the formula =Sheet1!A2*Sheet2!B2, which returns the result 6250.

Now if you want to look at that formula in the formula bar, and have it make sense with text to explain what those numbers refer to...

...instead of the formula on Sheet3 cell C1 of...
=Sheet1!A2*Sheet2!B2

...you could have
=Sheet1!A2*Sheet2!B2+N("50 times 125")

...or
=Sheet1!A2*Sheet2!B2+N("Price of widgets 50 times Sales of widgets 125")

This will still return 6250 but lets you see (as an "N" note appended to the formula) what numbers and factors are being calculated.

Just a start as I said, I'm sure others reading this post will know of more interesting undocumented or underdocumented features.

 

[Scott Huish]

Just to expand on Tom Urtis's post:

N is not a note function, what it does is this:
Returns a value converted to a number.

The text string is just evaluating to 0, so is not affecting the outcome of the sample formulas.

 

[nbrcrunch]

Yes, I've used the N() function before.

In fact, in a file I've named Comments.xls, I have documented the three ways Excel permits comments

1. Using comment boxes (which most know)
2. Using Validation comments which some know
3. Using the N() function, which few know. (I have nicknamed these "inline" comments.)

But has this thread died so quickly? I was surprised to only find 2 posts after two days. Must be the weekend.

 

[PA HS Teacher]

That's cool. I never though of Using N that way.

I've recently become enamored with the use of the Row function in array formulas and sumproduct formulas. You can use it kind of like a for next loop.

e.g.
=Row(1:20) array entered will return and array of numbers 1 to 20.

or in cojunction with the indirect function it can be used to loop through the characters of a string.

For example:
=MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)

array entered across multiple cells will return a single letter from the text in A1 in each cell.

 

[PA HS Teacher]

How about the following for commenting functions that return a string.

=A1&IF(1=0,"Make any comments you desire here. This has the advantage of working with strings as well. Doesn't seem to allow long comments, but its better than nothing.  If you need a number result you must coerce it with a +0, or -- etc..","")

 

[nbrcrunch]

okay, now that the weekend is over, time to shove this back up to the top of the heap.

 

[Aladin Akyurek]

...I've recently become enamored with the use of the Row function in array formulas and sumproduct formulas. You can use it kind of like a for next loop.

e.g.
=Row(1:20) array entered will return and array of numbers 1 to 20.

Using that idiom as is will invariably lead to non-robust formulas, compromising their correctness.

or in cojunction with the indirect function it can be used to loop through the characters of a string.

For example:
=MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)

array entered across multiple cells will return a single letter from the text in A1 in each cell.

Not without considerable performance cost...

 

[PA HS Teacher]

I recognize these limitations. I understand that invoking the volatile indirect is expensive, and invoking it in an array formula is really expensive. There are some situations I've encountered recently, mostly dealing with strings, where it would be nice to be able to loop through the characters of a string. Hypothetically speaking, if you wanted an array containing the positions of every "a" in abracadabra, you could use the following array formula:

=(MID(A1,ROW(1:1000),1)="a")*ROW(1:1000)

(I just picked 1000 because it is much larger than a string I'd likely encounter. I did this instead of using the indirect function to stop looping at the last character)

Is there another, more robust formula that will return the positions of all "a"s in a string located in A1?

 

[Aladin Akyurek]

I recognize these limitations. I understand that invoking the volatile indirect is expensive, and invoking it in an array formula is really expensive. There are some situations I've encountered recently, mostly dealing with strings, where it would be nice to be able to loop through the characters of a string. Hypothetically speaking, if you wanted an array containing the positions of every "a" in abracadabra, you could use the following array formula:

=(MID(A1,ROW(1:1000),1)="a")*ROW(1:1000)

(I just picked 1000 because it is much larger than a string I'd likely encounter. I did this instead of using the indirect function to stop looping at the last character)

Is there another, more robust formula that will return the positions of all "a"s in a string located in A1?

Insert a row before the formula cell and observe the result array. That's what I mean by robustness.

An option to avoid INDIRECT (here an almost perfect occasion for invoking the INDIRECT idiom) and to have the desired array result is:

{=IF(MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1)="a",ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))))}