+ Reply to Thread
Results 1 to 30 of 30

Half-Day Friday Question: Whats is your most used Function

  1. #1
    Registered User
    Join Date
    05-17-2012
    Location
    OKLAHOMA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Half-Day Friday Question: Whats is your most used Function

    Can you all help settle a minor disagreement between myself and some fellow cubicle convicts. This is for those that work in excel on a daily basis in a business environment?

    What is the function that you use most in excel on a daily basis?

    Me: Countifs .... The world would stop turning with out it

    What is yours?

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,609

    Re: Half-Day Friday Question: Whats is your most used Function?

    I think this thread is better suited for https://www.excelforum.com/the-water-cooler/ but anyway: when I look carefuly into my spredashettsts - simple IF is most frequent one
    Best Regards,

    Kaper

  3. #3
    Registered User
    Join Date
    12-20-2013
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    88

    Re: Half-Day Friday Question: Whats is your most used Function?

    I have a giant spreadsheet that uses this a lot. =IF(ISNA(MATCH(xxxxxxxxxxxx),"",INDEX(xxxxxxxxxxxxxxx)

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Half-Day Friday Question: Whats is your most used Function?

    pivot table / power pivot
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Half-Day Friday Question: Whats is your most used Function?

    Quote Originally Posted by oeldere View Post
    pivot table / power pivot
    We'd never have guessed that oeldere!!!

    Oh, and for me:

    INDEX-MATCH (not that anyone here would guess that, either...)
    Last edited by Glenn Kennedy; 09-01-2017 at 02:39 PM.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  6. #6
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Half-Day Friday Question: Whats is your most used Function?

    since we're confessing here mine is without a doubt the IF function. But for me it is used straight as IF / THEN or in combinations with vlookup, index/match, sumifs, countifs, sumproduct, search, etc.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Half-Day Friday Question: Whats is your most used Function?

    I think VLOOKUP was my first "magic moment".
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Half-Day Friday Question: Whats is your most used Function?

    TMS... not with Suzie, behind the bicycle shed
    ... or local equivalent???

  9. #9
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Half-Day Friday Question: Whats is your most used Function?

    That's like asking me to pick my favorite eye ... or grandchild.

    That said ... IF/INDEX/SMALL/ROW/MIN/ROWS/COLUMNS ... IFERROR ... I make a lot of those.
    Dave

  10. #10
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Half-Day Friday Question: Whats is your most used Function

    Work? Is that something people still do? Been so long since I've been in a Business Environment, I have forgotten.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  11. #11
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Half-Day Friday Question: Whats is your most used Function

    That got me curious, so I decided to write some code to fund out. Here's the histogram for a pricing template workbook I use every day (name changed):

    MyFile.xlsm
    Function
    Usage
    IF
    1655
    SUMPRODUCT
    516
    ROUND
    255
    VLOOKUP
    248
    SUMIF
    227
    N
    207
    SUM
    51
    SUBTOTAL
    43
    LOOKUP
    24
    IFERROR
    23
    SetIndent
    22
    INDEX
    16
    NA
    15
    MROUND
    4
    TRIM
    2
    TEXT
    2
    LEFT
    1
    RIGHT
    1
    FLOOR
    1


    Most of the formulas in the workbook are named formulas, so those don't appear.

    If you're interested in measuring some of your own,

    Please Login or Register  to view this content.
    Needs references to Microsoft Scripting Runtime and Regular Expressions.
    Entia non sunt multiplicanda sine necessitate

  12. #12
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Half-Day Friday Question: Whats is your most used Function

    The RegEx needs to be tweaked to find longer function names and functions with periods, like FORECAST.ETS.SEASONALITY.

    EDIT: Looks like this will work:

    Please Login or Register  to view this content.
    Last edited by shg; 09-01-2017 at 08:03 PM.

  13. #13
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Half-Day Friday Question: Whats is your most used Function

    @Glenn: far be it from me to drag this thread way off topic . My memory fails me but I think it WAS Susan, but I can't remember where . Anyway ... SUMPRODUCT was also a great find, long before SUMIFS came along.

  14. #14
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Half-Day Friday Question: Whats is your most used Function

    I processed 655 workbooks that I either posted on forums over the last 10 years or developed for my own interest.

    Function
    Usage
    INDEX
    263,645
    IF
    205,107
    MATCH
    110,827
    ROWS
    101,577
    SUM
    80,003
    COUNTIF
    75,547
    RAND
    74,629
    RANDBETWEEN
    58,399
    SUMPRODUCT
    53,048
    IFERROR
    43,738
    VLOOKUP
    36,277
    SIGN
    35,787
    LOOKUP
    35,701
    NA
    31,638
    MAX
    31,427
    CHOOSE
    28,601
    COLUMN
    27,254
    COLUMNS
    25,319
    MMULT
    24,363
    COS
    23,668
    CHAR
    23,272
    OR
    22,527
    ISNUMBER
    20,348
    NORM.DIST
    18,449
    COUNTIFS
    17,533
    SMALL
    16,102
    PRODUCT
    15,880
    MID
    15,455
    TRANSPOSE
    14,975
    MIN
    14,917
    ISNA
    14,477
    YEAR
    14,451
    INT
    13,931
    MOD
    13,713
    ROW
    12,647
    ABS
    12,476
    COUNT
    11,337
    TEXT
    10,073
    FREQUENCY
    8,775
    AND
    7,900
    NORM.INV
    6,261
    ROUND
    5,975
    FACT
    5,893
    CORREL
    5,860
    LOG
    5,412
    NORMINV
    4,976
    ISERROR
    4,852
    INDIRECT
    3,992
    N
    3,715
    SUMIF
    2,871
    TRIM
    2,745
    SIN
    2,672
    RANK
    2,647
    OFFSET
    2,370
    SQRT
    2,319
    EXP
    2,281
    SERIESSUM
    1,849
    LN
    1,688
    RADIANS
    1,643
    AVERAGE
    1,554
    LINEST
    1,358
    COMBIN
    1,301
    LEN
    1,276
    LARGE
    1,161
    SUBTOTAL
    966
    MROUND
    940
    EDATE
    913
    DATEDIF
    821
    NORMDIST
    819
    COUNTA
    759
    FIND
    726
    SUMSQ
    656
    PI
    654
    CODE
    643
    HLOOKUP
    516
    HYPERLINK
    510
    WEEKDAY
    498
    FLOOR
    494
    ACOS
    474
    CELL
    424
    DATE
    415
    WMA
    410
    DOLLAR
    400
    PROB
    400
    TABLE
    358
    NORMSINV
    300
    CEILING
    293
    SUMIFS
    291
    HMA
    282
    DEGREES
    281
    PERCENTILE
    272
    PERCENTRANK
    271
    TREND
    248
    SUBSTITUTE
    240
    MINVERSE
    231
    ISTEXT
    216
    MONTH
    200
    TIME
    200
    REPT
    184
    LOGNORMDIST
    174
    CONVERT
    156
    HOUR
    150
    MINUTE
    150
    BINOM.DIST
    121
    NOT
    120
    WBS
    100
    NORMSDIST
    93
    STDEV
    74
    LEFT
    61
    TODAY
    60
    ISODD
    55
    ISEVEN
    52
    DAY
    50
    SECOND
    50
    ADDRESS
    39
    LOGNORM.DIST
    39
    NORM.S.DIST
    38
    POISSON.DIST
    38
    ERFC
    36
    COVAR
    34
    BINOMDIST
    30
    LOGEST
    27
    STDEVP
    23
    MULTINOMIAL
    21
    DDB
    20
    LOGNORM.INV
    19
    CHIINV
    18
    ROUNDUP
    18
    RIGHT
    17
    TAN
    13
    GCD
    12
    AVERAGEIF
    11
    ISBLANK
    10
    VAR.P
    10
    IMDIV
    9
    GEOMEAN
    7
    GROWTH
    7
    ISERR
    7
    VAR
    7
    BETADIST
    5
    MDETERM
    5
    NPV
    5
    ATAN
    4
    ROUNDDOWN
    4
    SLOPE
    4
    ASIN
    3
    CONCATENATE
    3
    IRR
    3
    LCM
    3
    RSQ
    3
    SEARCH
    3
    MEDIAN
    2
    TDIST
    2
    DOLLARFR
    1
    INTERCEPT
    1
    NOW
    1
    PERMUT
    1
    QUOTIENT
    1
    STDEV.P
    1
    XIRR
    1
    XNPV
    1


    As you would expect, the data is a close fit to an exponential trendline.

    Workbook attached if anyone wants to play.
    Attached Files Attached Files
    Last edited by shg; 09-03-2017 at 03:24 PM.

  15. #15
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Half-Day Friday Question: Whats is your most used Function

    Applicable to 1 workbook:

    Please Login or Register  to view this content.



  16. #16
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Half-Day Friday Question: Whats is your most used Function

    Hey shg,

    Very cool. See attached my last few months of collected answers on EF. This is from 64 workbooks.

    EF000000 - Tally Function Usage from SHG.xlsm

    It looks like "IF()" is used more than "Index()" in my collection.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  17. #17
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Half-Day Friday Question: Whats is your most used Function

    Wow -- I used NOW once in 655 workbooks, and you used it 37,000 times in 65.

    You must be a very on-time guy!

  18. #18
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Half-Day Friday Question: Whats is your most used Function

    ... or very demanding.

  19. #19
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Half-Day Friday Question: Whats is your most used Function

    I used NOW once in 655 workbooks, and you used it 37,000 times in 65.
    I'm just running your code...

    I wonder if I could figure out how to find the workbook(s) that used all those "NOW()" functions?

  20. #20
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Half-Day Friday Question: Whats is your most used Function

    Windows File Search for NOW(), mebbe?

  21. #21
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Half-Day Friday Question: Whats is your most used Function

    Windows File search doesn't seem to look in/at formulas. Only FileNames and/or text strings are found.

  22. #22
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Half-Day Friday Question: Whats is your most used Function

    Find the red line and add the blue lines as shown:

    Please Login or Register  to view this content.

  23. #23
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Half-Day Friday Question: Whats is your most used Function

    Hey shg,

    Sorry but I keep getting a msgbox of

    No Functions Found!

    I've opened your workbook and clicked on Run. I goto my OneDrive, where I've stored lots of file and select about 10 of them. Click OK to select those files and it work a little while and then I get the above message. I have put your blue lines in the VBA module.

    Any other quick changes to find that file?

  24. #24
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Half-Day Friday Question: Whats is your most used Function

    OK,

    I found that file with all those "NOW()" in it. I couldn't get your fix above to work, but I could look at the filename in A1 and see when the Now count jumped. I then looked at Show Formulas and saw them. Just a single file created that Now count. See attached.. Not my file but simply one that I helped an OP with on the forum.

    Set Date on Data entered.xlsm

    Now - I'm not so needy!!

  25. #25
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Half-Day Friday Question: Whats is your most used Function

    Good job, Marvin.

    That is a disturbing file.

  26. #26
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Half-Day Friday Question: Whats is your most used Function

    I really don't use formulas hence functions very much - this book has been my stand-by for 7 years:

    Data Range
    A
    B
    1
    Forum10.xlsm
    2
    Function
    Usage
    3
    IF
    39
    4
    TxtToNumX
    13
    5
    Dede
    10
    6
    GetAlpha
    6
    7
    SUM
    6
    8
    MMULT
    5
    9
    MINVERSE
    5
    10
    COUNTIF
    4
    11
    LOG
    3
    12
    AVERAGE
    3
    13
    DoneDAte
    2
    14
    CEILING
    2
    15
    NUMBERVALUE
    2
    16
    WEBSERVICE
    2
    17
    CONCATENATE
    2
    18
    GetP
    2
    19
    GetQ
    2
    20
    GetMedian
    1
    21
    SUMPRODUCT
    1
    22
    UPPER
    1
    23
    Digit
    1
    24
    MAX
    1
    25
    IMSQRT
    1
    26
    ISTEXT
    1
    27
    INT
    1
    28
    SIN
    1
    29
    TODAY
    1
    30
    Repc
    1
    31
    CCHiLo
    1
    32
    CCHiLoX
    1
    33
    ATAN
    1
    34
    PI
    1
    35
    SQRT
    1
    36
    DecLen
    1
    37
    dBavg
    1

    Is there code to find the functions used in VBA?
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  27. #27
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Half-Day Friday Question: Whats is your most used Function

    Reckon you could use about the same code except that ...

    o In formulas, a valid function name followed by opening paren is ALWAYS a function, and vice-versa. In VBA, it could be an array or a Sub.

    o In VBA, unlike formulas, functions that don't require arguments can omit the parens (e.g., Now, Rnd, Dir, ...)

    o Maybe some other reasons.

    It might be easiest to make a list of all the functions, add them to a dictionary, and go from there.

  28. #28
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Half-Day Friday Question: Whats is your most used Function

    @ shg - thanks - I'll pass (I'm no Genuis)

  29. #29
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Half-Day Friday Question: Whats is your most used Function

    For me, from 22 Jul 2011, the most used function is:

    HELP function: 4272

    (Excluding this post, of course)
    Quang PT

  30. #30
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Half-Day Friday Question: Whats is your most used Function

    As I've spent the last few months moving pretty much everything I do into SSAS Tabular, then CUBEVALUE is probably my most used, these days.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 10-20-2014, 03:09 PM
  2. Replies: 9
    Last Post: 12-19-2012, 01:06 AM
  3. How to count all dates in column A using last friday and and next friday friday
    By rain4u in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-16-2011, 04:33 PM
  4. Replies: 4
    Last Post: 02-11-2011, 08:02 AM
  5. Replies: 2
    Last Post: 08-15-2006, 03:20 PM
  6. [SOLVED] Another Friday Question
    By Jeremy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-23-2005, 06:06 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1