# Thread: Formula returning false when it shouldn't

1. ## 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. Can you post a small workbook with some data that shows the issue?

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. 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.

5. 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")`

6. 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. 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?

8. 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. 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. 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

11. 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. How do you us using INDIRECT() and ROW() I have never used them prior

13. 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

14. 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. Originally Posted by boccuz
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

16. 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. S4 should be yes when Sheet 4, O32 >=1, but is returning No.

18. Originally Posted by boccuz
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```

19. 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```

20. 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
•