Results 1 to 14 of 14
  1. #1
    Gold Lounger
    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.

    Cheers
    Regards,
    Rudi

  2. #2
    Gold Lounger
    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 27-Jan-09 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

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  4. #4
    Gold Lounger
    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!

    TX
    Regards,
    Rudi

  5. #5
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 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
    Attached Files Attached Files

  6. #6
    Gold Lounger
    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

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts

    Re: Excels MOST useful formulas (Excel (All))

    I find I use
    OFFSET
    and
    INDIRECT
    quite a lot

    zeddy

  8. #8
    Gold Lounger
    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 Zeddy
    Regards,
    Rudi

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excels MOST useful formulas (Excel (All))

    OFFSET is very useful in combination with MATCH, for example in lookup-to-the-left.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excels MOST useful formulas (Excel (All))

    Oh, and take a look at Dynamic Named Ranges - they also use OFFSET.

  11. #11
    Gold Lounger
    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 Chart
    Regards,
    Rudi

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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>

  13. #13
    Gold Lounger
    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

  14. #14
    Gold Lounger
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •