Results 1 to 14 of 14

20090127, 08:19 #1
 Join Date
 Feb 2004
 Location
 Cape Town, RSA
 Posts
 3,444
 Thanks
 0
 Thanked 1 Time in 1 Post
Excels MOST useful formulas (Excel (All))
Hi,
I am currently compiling a list of (arguably) Excels MOST useful formulas. I am planning to set up a short training course around this topic and would like to get some input as to what you think is a valuable formula/function in Excel. Since there are soooooo many functions available, I am not after the standard functions like IF and VLOOKUP, etc... I am after those amazing or special ones that are not "common". For example what Jan Karel mentioned the other day on IF(INDEX(MATCH....), in <post#=754,382>post 754,382</post#>, which is faster than VLOOKUP, or array functions that many people don't know or consider using, like {SUM(IF(...),IF(...))}.
Even if you can point me to a thread in the lounge with one or two unique/interesting/special formulas that can do amazing or uncommon things in Excel.
I know I can sit and search for hours myself, but if 10 loungers can point me to some threads or inform me of an example, it will same me so much time and personal effort.
Much appreciated for your help. I will be grateful for amy leads.
CheersRegards,
Rudi

20090127, 08:25 #2
 Join Date
 Feb 2004
 Location
 Cape Town, RSA
 Posts
 3,444
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Excels MOST useful formulas (Excel (All))
<P ID="edit" class=small>(Edited by Rudi on 27Jan09 11:25. Adding additional examples...)</P>Conditional arrays:
Here is a post from Hans that has more examples of these "Special" formulas I am refering to: <post#=738,134>post 738,134</post#> <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
Reverse Name and Surname
And yet another thread that shows how to use Excels powerhouse formulas to accomplish a "non mathematical" result: The thread starts at <post#=751,759>post 751,759</post#>Regards,
Rudi

20090127, 09:13 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Excels MOST useful formulas (Excel (All))
The second thread mentions Bob Umlas' Array Formulas which describes a lot of unusual things you can do with array formulas.
Many of the SUMPRODUCT and SUM(IF(...)) formulas posted here in the Lounge are workarounds for SUMIF and COUNTIF with multiple conditions. If your audience uses Excel 2007, you can mention that multiple conditions are finally supported directly by the new functions SUMIFS and COUNTIFS.

20090127, 10:52 #4
 Join Date
 Feb 2004
 Location
 Cape Town, RSA
 Posts
 3,444
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Excels MOST useful formulas (Excel (All))
This is a great link Hans. Thanks for pointing me to these sample array formulas. There is a lot of interesting ideas and solutions to problems that can be encountered on a spreadsheet.
It often amazes me how extraordinarily powerful Excel's functions are  the fact that you can string a few together to do amazing tasks and not even mentioning the array options. It makes one wonder what CANNOT be done with a creative mind and an Excel formula or two!
TXRegards,
Rudi

20090127, 13:45 #5
 Join Date
 Nov 2002
 Location
 New York, New York, USA
 Posts
 275
 Thanks
 0
 Thanked 20 Times in 20 Posts
Re: Excels MOST useful formulas (Excel (All))
Rudi:
Attached is an article from Microsoft about how to use Excel to compare two lists. It not only show the power of functions but also how to use Excel to better organize data.
Plus it a very popular item with Users.
Regards,
Tom Duthie

20090127, 13:52 #6
 Join Date
 Feb 2004
 Location
 Cape Town, RSA
 Posts
 3,444
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Excels MOST useful formulas (Excel (All))
Many thanks Tom for your addition. Its great!!!
Regards,
Rudi

20090127, 14:17 #7
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,251
 Thanks
 154
 Thanked 613 Times in 583 Posts
Re: Excels MOST useful formulas (Excel (All))
I find I use
OFFSET
and
INDIRECT
quite a lot
zeddy

20090127, 15:13 #8
 Join Date
 Feb 2004
 Location
 Cape Town, RSA
 Posts
 3,444
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Excels MOST useful formulas (Excel (All))
Strange...those are two that I hardly ever use. It depends on what tasks you perform in Excel.
TX ZeddyRegards,
Rudi

20090127, 15:19 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Excels MOST useful formulas (Excel (All))
OFFSET is very useful in combination with MATCH, for example in lookuptotheleft.

20090127, 15:30 #10
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Excels MOST useful formulas (Excel (All))
Oh, and take a look at Dynamic Named Ranges  they also use OFFSET.

20090127, 18:00 #11
 Join Date
 Feb 2004
 Location
 Cape Town, RSA
 Posts
 3,444
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Excels MOST useful formulas (Excel (All))
Ah, yes...this rings some old bells. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
I had to dig deeeeep down for this thread... <img src=/S/wink.gif border=0 alt=wink width=15 height=15>
See <post#=394,337>post 394,337</post#> for an example of a Scrolling Chart using dynamic range names,
and
See <post#=466,223>post 466,223</post#> for an example of a Changing Range ChartRegards,
Rudi

20090127, 18:04 #12
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Excels MOST useful formulas (Excel (All))
So you *knew* about the power of the OFFSET function... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

20090127, 18:19 #13
 Join Date
 Feb 2004
 Location
 Cape Town, RSA
 Posts
 3,444
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Excels MOST useful formulas (Excel (All))
Its the *only* time I ever use OFFSET. <img src=/S/shy.gif border=0 alt=shy width=15 height=15>
Its a cool trick though!Regards,
Rudi

20090128, 07:43 #14
 Join Date
 Feb 2004
 Location
 Cape Town, RSA
 Posts
 3,444
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Excels MOST useful formulas (Excel (All))
Thanks to Torquemada, Hans has provided another interesting function that I have added to this thread.
See it here: <post#=755588>post 755588</post#>
Its seems like an array within another array. I must delve into this a bit more. The only other time I have seen this is in the functions LARGE and SMALL where one can set it up like this:
=LARGE(A1:A100,{1,2,3,4,5}) to return the 5 largest values in the range. The function itself is not confirmed as an array function! So you simply ENTER (not CTRL+SHIFT+ENTER)Regards,
Rudi