Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 33

Thread: Formula returning false when it shouldn't

  1. #1
    VBAX Regular
    Joined
    Jan 2018
    Posts
    21
    Location

    Formula returning false when it shouldn't

    Hello, I created the following formula to return either Yes or No. The return of Yes is working corrcetly. But No arguments are returning as false. I cant figure out why. Can anyone help please.

    =IF(D4="M",IF(SUM('5'!$O$32:$O$42)>=5,"Yes",IF('5'!$O$32>=1,"Yes","No")))

    Thanks you

  2. #2
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    5,928
    Location
    Can you post a small workbook with some data that shows the issue?
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    Try trimming the cells you're trying to match and make sure the the format of the summed cells is correct (i.e. not text) - that can be the issue sometimes.

    But like Paul said, we can help diagnose the problem quicker if you share your data set with us.

  4. #4
    VBAX Regular
    Joined
    Jan 2018
    Posts
    21
    Location
    Here is a condensed version of the spreadsheet I am working with. Should be enough to get the formula to work. I am only looking to change the formula in Sheet 1, Column S.
    Attached Files Attached Files

  5. #5
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    5,928
    Location
    S4 and S5 have different formulas which don't think is intentional

    Capture.JPG

    Try this in S4 and copy down


    =IF(D4="M",IF(SUM('5'!$O$32:$O$42)>=5,"Yes",IF('5'!$O$32>=1,"Yes","No")),"No")
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  6. #6
    VBAX Regular
    Joined
    Jan 2018
    Posts
    21
    Location
    Master_Service_Award.xlsm

    Thanks, but still not working. I am still getting False.

    I attached an updated sheet.

    In my previous attachment, I am trying to change the original formula in cell S% to the one I am updating in S4. I left the original formula in the cell as it worked previously.

  7. #7
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    5,928
    Location
    I gave you the formula for S4. It looks like you put it in S5??

    Capture.JPG

    Fixing your S5 formula and filling it into S4 ...

    Capture1.JPG

    Seems to return the No in S4 and the Yes in S5 that I thought you were expecting

    Capture2.JPG

    Am I failing to understand?
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  8. #8
    VBAX Regular
    Joined
    Jan 2018
    Posts
    21
    Location
    Here is what I am trying to do

    a name is assigned either an "M" or "A" in sheet 1 column A
    The sheet that I work with has 50 sheets. the sheets are assigned to an individual and are numbered according to row number of the individuals name.
    for names that are assigned "M" the total of O32:O42 of their individual sheet must total at least 5 and return yes, if less than 5 must return no.
    for names that are assigned "A" the total of O32 of their individual sheet must total at least 1 and return yes, if zero must return no.

    My thought in creating the formula;

    if the value in column D*="M" then the true argument IF(SUM('5'!$O$32:$O$42)>=5, will return "Yes"
    the false argument would mean D*="A" so i used a second if argument IF('5'!$O$32>=1, to validate if true and return "Yes"
    The false argument would mean that both the "M" and "A" argument is false and return "NO"

  9. #9
    VBAX Regular
    Joined
    Jan 2018
    Posts
    21
    Location
    I am sorry. I meant

    a name is assigned either an "M" or "A" in sheet 1 column D

    The formula should follow the following sequence

    Row 4: =IF(D4="M",IF(SUM('4'!$O$32:$O$42)>=5,"Yes",IF('4'!$O$32>=1,"Yes","No")))

    Row 5: =IF(D5="M",IF(SUM('5'!$O$32:$O$42)>=5,"Yes",IF('5'!$O$32>=1,"Yes","No")))

    Row 6: =IF(D6="M",IF(SUM('6'!$O$32:$O$42)>=5,"Yes",IF('6'!$O$32>=1,"Yes","No")))

    etc (will follow sequence to row 50.)

  10. #10
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    5,928
    Location
    Ok, then try this

    I think you were getting FALSE instead of the expected "No" because the first IF didn't have a value for a false condition, and so it defaulted to just False

    Row 4: =IF(D4="M",IF(SUM('4'!$O$32:$O$42)>=5,"Yes",IF('4'!$O$32>=1,"Yes","No")), "No")
    
    Row 5: =IF(D5="M",IF(SUM('5'!$O$32:$O$42)>=5,"Yes",IF('5'!$O$32>=1,"Yes","No")), "No")
    
    Row 6: =IF(D6="M",IF(SUM('6'!$O$32:$O$42)>=5,"Yes",IF('6'!$O$32>=1,"Yes","No")), "No")

    I'd suggest that you look into using INDIRECT() and ROW() to avoid a lot of typing where the only different in 46 rows it the referenced sheet name
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  11. #11
    VBAX Regular
    Joined
    Jan 2018
    Posts
    21
    Location
    getting close, no longer getting FALSE.

    Formula is working correctly for the "M", but not the "A" I should be getting "yes" with a value of 1, but am getting No

    I really do appreciate your help.


    Master_Service_Award.xlsm

  12. #12
    VBAX Regular
    Joined
    Jan 2018
    Posts
    21
    Location
    How do you us using INDIRECT() and ROW() I have never used them prior

  13. #13
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    5,928
    Location
    Put this in S4 and fill down

    =IF(INDIRECT("D"&ROW())="M",IF(SUM(INDIRECT("'"&ROW()&"'!$O$32:$O$42"))>=5,"Yes",IF(INDIRECT("'"&ROW()&"'!$O$32")>=1,"Yes","No")),"No")
    Online help has pretty good explanation
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  14. #14
    VBAX Regular
    Joined
    Jan 2018
    Posts
    21
    Location
    I am still getting "NO" when D*=A and O32=1, should result as "yes"
    I see how indirect works now.. Thanks for the tip!

  15. #15
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    5,928
    Location
    Quote Originally Posted by boccuz View Post
    Hello, I created the following formula to return either Yes or No. The return of Yes is working corrcetly. But No arguments are returning as false. I cant figure out why. Can anyone help please.

    =IF(D4="M",IF(SUM('5'!$O$32:$O$42)>=5,"Yes",IF('5'!$O$32>=1,"Yes","No")))

    Thanks you
    Back to the beginning

    I assume that this should really be to worksheet "4"

    =IF(D4="M",IF(SUM('4'!$O$32:$O$42)>=5,"Yes",IF('4'!$O$32>=1,"Yes","No")))
    In pseudocode, is this the correct logic

    If D4 = "M" Then
    
         if SUM('4'!$O$32:$O$42)>=5 Then
            S4 = "Yes"
        
         ElseIf '4'!$O$32>=1 Then
                S4 = "Yes"
         Else
                S4 = "No"
         End IF
    
    Else
        S4 = "No"
    EndIf
    D4 = "A" s
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  16. #16
    VBAX Regular
    Joined
    Jan 2018
    Posts
    21
    Location
    Yes, this would be for sheet 4 =IF(D4="M",IF(SUM('4'!$O$32:$O$42)>=5,"Yes",IF('4'!$O$32>=1,"Yes","No"))) and return argument in S4 (D4=A)

    this would be for sheet 5 =IF(D5="M",IF(SUM('5'!$O$32:$O$42)>=5,"Yes",IF('5'!$O$32>=1,"Yes","No"))) and return argument in S5 (D5=M)

    Yes, in pseudocode, that is the correct logic.

    S4 should be yes when Sheet for O32 >=1

  17. #17
    VBAX Regular
    Joined
    Jan 2018
    Posts
    21
    Location
    S4 should be yes when Sheet 4, O32 >=1, but is returning No.

  18. #18
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    5,928
    Location
    Quote Originally Posted by boccuz View Post
    S4 should be yes when Sheet 4, O32 >=1, but is returning No.
    That doesn't agree with the pseudocode in #15 that you said was correct

    Since D4 is NOT = "M" the final Else returns "No"

    If D4 = "M" Then   <<<<<<<<<<<<<<<<<<<<<<<<<<<<<< if not "M"
    
         if SUM('4'!$O$32:$O$42)>=5 Then
            S4 = "Yes"
        
         ElseIf '4'!$O$32>=1 Then
                S4 = "Yes"
         Else
                S4 = "No"
         End IF
    
    Else <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< goes here
        S4 = "No"
    EndIf
    I'm guessing that there are different checks for M's and A's????

    Is this any closer to the logic?

    If D4 = "M" Then
    
         if SUM('4'!$O$32:$O$42)>=5 Then
            S4 = "Yes"
        Else
            S4 = "No"
        Endif
    
    Elseif D4 = "A" Then
         If '4'!$O$32>=1 Then
                S4 = "Yes"
         Else
                S4 = "No"
         End IF
    
    Else
        S4 = "No"
    EndIf
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  19. #19
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    5,928
    Location
    I'd use a User Defined Function since I (personally) don't like to use long complicated worksheet formulas


    Option Explicit
    
    Function EarnedMinimum() As Variant
        Dim ws As Worksheet
        Dim r As Long
        Dim rng As Range
        
        Application.Volatile
        
        r = Application.Caller.Row
        
        Set ws = Worksheets(CStr(r))
        Set rng = Application.Caller.Parent.Rows(r)
        
        If Len(rng.Cells(4).Value) = 0 Then
                EarnedMinimum = vbNullString
        
        ElseIf rng.Cells(4).Value = "M" Then
            If Application.WorksheetFunction.Sum(Worksheets(CStr(r)).Range("$O$32:$O$42")) >= 5 Then
                EarnedMinimum = "Yes"
            Else
                EarnedMinimum = "No"
            End If
            
        ElseIf rng.Cells(4).Value = "A" Then
            If Worksheets(CStr(r)).Range("$O$32").Value >= 1 Then
                EarnedMinimum = "Yes"
            Else
                EarnedMinimum = "No"
            End If
        
        Else
            EarnedMinimum = CVErr(xlErrValue)
        End If
    End Function
    Attached Files Attached Files
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  20. #20
    VBAX Regular
    Joined
    Jan 2018
    Posts
    21
    Location
    wow.. Thanks. So I just copy the cell down to row 50?

Posting Permissions

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