+ Reply to Thread
Results 1 to 3 of 3

Understanding this formula...

  1. #1
    Registered User
    Join Date
    10-04-2018
    Location
    Washington
    MS-Off Ver
    Excel for Mac Version 16
    Posts
    1

    Understanding this formula...

    Good morning,

    I recently gained the responsibility of an excel spreadsheet, which is used to track the training progress of individual sailors. However, my excel knowledge is basic if not non-existent. I'm having a hard time understanding what this particular formula is doing...

    Screen Shot 2018-10-04 at 11.20.14 PM.png

    If anyone can help break down what each formula is doing to the overall formula I'd much appreciate it. Thank you in advance.

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    5,959

    Re: Understanding this formula...

    Hello and welcome to the forum.

    That formula is a way to return multiple matches (and can actually be shortened if you wanted to).

    The SMALL(IF(...(ROW(...)))) portion of the formula returns the first row number (because ROW(A1)=1) where the criteria, Personnel_ACTC_Level="SS4 (100)", is TRUE.
    Then when you drag the formula down a row, it returns the second row number (because ROW(A2)=2) where that criteria is TRUE.
    etc.

    The INDEX function then looks at Personnel_Names and returns the matching value given the row number from above.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,007

    Re: Understanding this formula...

    Hi, welcome to the forum

    1. Also note that that is an ARRAY formula, ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    2. Not sure if you looked at the pic you provided, but my tired old eyes can barely read anything, it is so small. To be of more help, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ 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. Help understanding formula please.
    By DBExcelDB in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-22-2018, 03:07 PM
  2. [SOLVED] understanding formula
    By geniusufo007 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-24-2017, 07:02 AM
  3. [SOLVED] Understanding formula's
    By madhatter40 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-09-2013, 12:52 PM
  4. Help understanding a formula
    By excel0124 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 04-15-2013, 02:56 PM
  5. Need help understanding a formula
    By mikedonka in forum Excel General
    Replies: 2
    Last Post: 05-07-2012, 03:07 AM
  6. need help understanding a formula
    By imrainbow in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-09-2011, 01:12 AM
  7. Understanding a formula
    By Jordan in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-26-2005, 05:15 PM

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