+ Reply to Thread
Results 1 to 10 of 10

Help with If and Function

  1. #1
    Registered User
    Join Date
    03-24-2013
    Location
    Somewhere between lost and found, CA
    MS-Off Ver
    Excel 2010
    Posts
    7

    Help with If and Function

    I need help, I know Its got to be a simple fix, but...

    I have a form where there are 20 questions. When a question is missed or marked NO I want the number of that question to appear in a comments section. So only questions where the answer is "no" appear in the comments section. Here is the problem. I have used the If function to get the first missed question no problem but when the second one occurs it only finds the first. What part of the formula am I missing? so if Question 1, in cell b1, is "no" I get the question number in a1, placed in a7 Ok. Here is the concern: if cell b1 is no and b2 is no I want cell a7 to be "1" and a8 to be "2" sequentially. I also want A7 to =2 if question 1 is "yes" but question 2 is "no".

    I hope i have explained this correctly...


    in cell a7 I have =if(b1=NO,a1,if(b2=no,a2,""))

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Help with If and Function

    Hi blowout100,

    welcome to the forum.

    Can you explain using an excel workbook ? Thanks.


    Regards,
    DILIPandey
    <click on below * if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  3. #3
    Registered User
    Join Date
    03-24-2013
    Location
    Somewhere between lost and found, CA
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Help with If and Function

    here is the form
    Attached Files Attached Files

  4. #4
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Help with If and Function

    Okay... use below formula in column A:-

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    see attached: Copy questions forum.xlsx



    Regards,
    DILIPandey
    <click on below * if this helps>

  5. #5
    Registered User
    Join Date
    03-24-2013
    Location
    Somewhere between lost and found, CA
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Help with If and Function

    Attachment 223248Copy questions forum.xlsxWhat I need is for A29 to A40 to fill in with question numbers were a NO is selected in column M. I inserted the formula buy now when Question one is a NO cell A29 is 0. I need the first question answered with a NO, the question number, is placed in A29. so all the No question numbers will be listed in A29 to A40....

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Help with If and Function

    If I understand what you want to do...

    Enter this array formula** in A30 and copy down to A40:

    =IFERROR(INDEX(A$5:A$27,SMALL(IF(M$5:M$27="No",ROW(M$5:M$27)),ROWS(A$30:A30))-ROW(M$5)+1),"")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  7. #7
    Registered User
    Join Date
    03-24-2013
    Location
    Somewhere between lost and found, CA
    MS-Off Ver
    Excel 2010
    Posts
    7

    Thumbs up Re: Help with If and Function

    Thats IT!!!!!!!!!!!!! Thanks awesome!!!!!!!!!!!

  8. #8
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Help with If and Function

    You're welcome. Thanks for the feedback!

  9. #9
    Registered User
    Join Date
    03-24-2013
    Location
    Somewhere between lost and found, CA
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Help with If and Function

    Can you walk me thru what the formula means?

    The row(s) and index are confusing me just in case i need to edit....

  10. #10
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Help with If and Function

    Sure thing.

    I won't be able to get to it until after the Penguins game tonight.

    Stay tuned...

  11. #11
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Help with If and Function

    Here you go...

    Sure...

    Let's use a smaller data set to see what's happening in that
    formula.

    Let's assume this is our data:

    A5: 1 ... M5: Yes
    A6: 2 ... M6: No
    A7: 3 ... M7: Yes
    A8: 4 ... M8: Yes
    A9: 5 ... M9: No

    We want to list the store numbers in A5:A9 that correspond
    to a "No" entry in M5:M9.

    This array formula entered in cell A30 and copied down to
    A34 will return these results:

    =IFERROR(INDEX(A$5:A$9,SMALL(IF(M$5:M$9="No",ROW(M$5:M$9)),ROWS(A$30:A30))-ROW(M$5)+1),"")

    A30: 2
    A31: 5
    A32: [blank]
    A33: [blank]
    A34: [blank]

    Here's how it works...

    I like to think of the INDEX function as a sort of "GPS" function.

    We index a range and then we tell it the row/column
    co-ordinates of where to find the data we want to return. In this application the data we want to return is in a single column, A5:A9, so we need to tell it which rows contain the data we want to return.

    INDEX(A$5:A$9

    The indexed cells A5:A9 are located in "positions" relative to
    the range. For example:

    A5 = position 1
    A6 = position 2
    A7 = position 3
    A8 = position 4
    A9 = position 5

    Based on the sample data, if we enter this formula:

    =INDEX(A5:A9,3)

    Return the value of the cell in position 3 of the indexed range
    A5:A9. The result we get is 3.

    In the formula we are using the IF function to return the absolute row numbers which will be converted to the relative position numbers.

    IF(M$5:M$9="No",ROW(M$5:M$9))

    Where the logical test is TRUE it returns the absolute row number of the cell(s). Where the logical test is FALSE it returns FALSE.

    M5 (Yes) = No = FALSE
    M6 (No) = No = 6
    M7 (Yes) = No = FALSE
    M8 (Yes) = No = FALSE
    M9 (No) = No = 9

    This array is then passed to the SMALL function:

    SMALL({FALSE;6;FALSE;FALSE;9}

    We use the ROWS function to then pass the nth smallest row number to the INDEX function.

    ROWS(A$30:A30)

    ROWS simply returns the count of rows in the referenced range.

    ROWS(A$30:A30) = 1

    As we drag copy the formula down a column the range reference increments like this:

    ROWS(A$30:A30) = 1
    ROWS(A$30:A31) = 2
    ROWS(A$30:A32) = 3
    ROWS(A$30:A33) = 4
    ROWS(A$30:A34) = 5

    In cell A30 the formula would evaluate to this:

    INDEX(A$5:A$9,SMALL({FALSE;6;FALSE;FALSE;9},1)

    Pass the 1st smallest row number (6) to the INDEX function:

    INDEX(A$5:A$9,6)

    Return the value of the cell in position 6 of the indexed range
    A5:A9.

    Now we have a slight problem! There is no position 6 in the indexed range. So, we need to calculate an "offset correction" that converts the absolute row number to its relative position number for the INDEX function. We do that using this expression:

    -ROW(M$5)+1

    Now we have:

    INDEX(A$5:A$9,6-ROW(M$5)+1)

    The ROW function returns the row number of the referenced range. In this case ROW(M$5) = 5. So:

    6-5+1 = 2

    INDEX(A$5:A$9,2) = A6 = store number 2

    As we drag copy the formula down the column we get:

    A30: INDEX(A$5:A$9,2) = A6 = store number 2
    A31: INDEX(A$5:A$9,5) = A9 = store number 5
    A32: INDEX(A$5:A$9,#NUM!) = #NUM!
    A33: INDEX(A$5:A$9,#NUM!) = #NUM!
    A34: INDEX(A$5:A$9,#NUM!) = #NUM!

    Since there are only 2 entries that meet the criteria if we copy the formula to more than 2 cells we will end up getting errors. We don't want to see those errors so we use the IFERROR function to return a blank "" instead of the error.

    So, this array formula entered in A30 and copied down to A34:

    =IFERROR(INDEX(A$5:A$9,SMALL(IF(M$5:M$9="No",ROW(M$5:M$9)),ROWS(A$30:A30))-ROW(M$5)+1),"")

    Returns these results:

    A30: 2
    A31: 5
    A32: [blank]
    A33: [blank]
    A34: [blank]

    Hopefully, that isn't too confusing!
    Last edited by FDibbins; 04-05-2014 at 03:49 PM. Reason: small change requested by Tony

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1