+ Reply to Thread
Results 1 to 3 of 3

Understanding how this [formula] works

  1. 05-24-2013, 07:52 PM #1
    jmendenhall22
    jmendenhall22 is offline
    Registered User
    Join Date
    07-05-2011
    Location
    Cincinnati, OH
    MS-Off Ver
    Mac Excell 2011
    Posts
    10

    Understanding how this [formula] works

    I have the following codes in excell workbooks and want to modify what they do but cannot make them work. I thought I knew what each part did but this is not the case (I found out). I am hoping if someone can explain what each part does by breaking it down then not only will I learn but I should be able to modify!!!

    =IFERROR(INDEX(CHARTASSIGN!$C$3:$C$30,SMALL(IF(LEFT(CHARTASSIGN!$C$3:$C$30,3)<>"DR.",0,100)+IF(CHARTASSIGN!$C$3:$C$30>"",0,100)+ROW(CHARTASSIGN!$S$3:$S$30)-2,ROWS(CHARTASSIGN!$C$3:$C3)),0),"")



    =IFERROR(INDEX(sheet2!$D$4:$D$74,SMALL(IF(LEFT(sheet2!$D$4:$D$74,3)<>"7,7mtg",0,100)+IF(sheet2!$D$4:$D$74>"",0,100)+ROW(sheet2!$S$3:$S$30)-2,ROWS(sheet2!$D$D4$D4)),0),"")

    I have changed them and used the ctrol shift enter keys but get no results.
    Thank you for looking and or explaining.
    John
    Last edited by JBeaucaire; 05-24-2013 at 08:21 PM. Reason: Moved to correct forum.
    Reply With QuoteRegister To Reply
  2. 05-24-2013, 08:45 PM #2
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Understanding how this [formula] works

    You have to work from the inside out.

    =IFERROR(INDEX(CHARTASSIGN!$C$3:$C$30,SMALL(IF(LEFT(CHARTASSIGN!$C$3:$C$30,3)<>"DR.",0,100)+IF(CHARTASSIGN!$C$3:$C$30>"",0,100)+ROW(CHARTASSIGN!$S$3:$S$30)-2,ROWS(CHARTASSIGN!$C$3:$C3),0),"")

    This formula creates an array that lists all the row numbers that hold "TRUE" for all the tests done inside. Then the SMALL function is used to take one of those rows and display a value from that row.

    The first value is retrieved using the number 1 in the second parameter of the SMALL(array, k) function. That number one is generated in the first cell by this part of the formula near the end:
    ROWS(CHARTASSIGN!$C$3:$C3)

    ROWS(C3:C3) = 1 row which = 1

    Each time you copy the formula down, eveything in the formula stays exactly the same execpt that part of the formula. The next row down that part will change to:
    ROWS(CHARTASSIGN!$C$3:$C4)

    ROWS(C3:C4) = 2 rows which = 2

    The rest is pretty standard. The array in the SMALL(array, k) function is being built by all the IF(tests+tests+test, ROWS(Range)) in your formula, and I think you understand that. Put an AND method is what I typically see, not plus signs:
    IF(AND(test, tests, test), ROWS(Range))

    Now the SMALL array is built, the first cell is ready to pull out the first value in the array. That is result is fed into the INDEX(range, row) as the "row" answer, and that mathing value from the "range" is revealed.

    So, your formula, I might write it as:

    =IFERROR(INDEX(CHARTASSIGN!$C$3:$C$30,SMALL(IF(--((LEFT(CHARTASSIGN!$C$3:$C$30,3)<>"DR.")*(CHARTASSIGN!$C$3:$C$30<>"")), ROW(CHARTASSIGN!$S$3:$S$30)-2),ROWS(CHARTASSIGN!$C$3:$C3))),"")


    You can click on the Formula > Evaluate Formula function to watch the formula unfold slowly.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)
    Reply With QuoteRegister To Reply
  3. 05-24-2013, 08:47 PM #3
    Chance2
    Chance2 is offline
    Forum Contributor
    Join Date
    04-01-2009
    Location
    Irvine, CA
    MS-Off Ver
    Excel 2010
    Posts
    280

    Re: Understanding how this [formula] works

    John,

    There are basically three parts to these formulas:
    1) IFERROR(condition, result): The condition is INDEX(...) and the result is the final ,""). Simply said return a blank if the formula produces an error.
    2) INDEX(array, row, column): Return the value from the array in row/column (in this case the column is the only one selected C on sheet CHARTASSIGN). The row is using the SMALL and ROW commands to determine which row to return.
    3) SMALL(array, nth value): This returns the number of the nth smallest value in your array. i.e. - If your array was 1, 3, 5, 7 and your nth value was 2 it will return 3 (the second smallest number). The trick here is in the IF and ROW statements. The IF statements are each looking to see if a condition is met If the first 3 characters of the search range are not "DR." then 0, otherwise 100 + If the search range is not blank then 0, otherwise 100 and finally return the row number (-2 to reset to 1 as your start). The IF + IF + ROW is making up a list of numbers for the SMALL to search through (If Cell C3 was "DR. NO" then the value would be 100 + 0 + 1 = 101, If Cell C4 was blank then it would be 0 + 100 + 2 = 102, If cell C5 was "JAMES BOND" then it would be 0 + 0 + 3 = 3 and so on down to C30. The last ROWS statement is finding which row your formula is on to determine the nth value. So if this formula was in A1 it would look for the smallest value (in my example 3) and return the value in C3 as the result.
    If this was copied down to A2 (and no more data below C5) would error out because the second smallest value is 101 which is outside the search range (C3:C30).
    This is an array formula, so it has to be entered with CSE.

    The second formula is doing basically the same thing on Sheet2 except it has an error in the final ROWS statement:
    ROWS(sheet2!$D$D4$D4) should be ROWS(sheet2!$D4:$D4).
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1