PDA

View Full Version : [SOLVED] Formula returning false when it shouldn't



boccuz
02-12-2018, 04:27 PM
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

Paul_Hossler
02-12-2018, 05:05 PM
Can you post a small workbook with some data that shows the issue?

m14020256
02-12-2018, 11:14 PM
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.

boccuz
02-13-2018, 08:23 AM
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.

Paul_Hossler
02-13-2018, 02:45 PM
S4 and S5 have different formulas which don't think is intentional

21612

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")

boccuz
02-13-2018, 05:06 PM
21613

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.

Paul_Hossler
02-13-2018, 05:31 PM
I gave you the formula for S4. It looks like you put it in S5??

21614

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

21615

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

21616

Am I failing to understand?

boccuz
02-13-2018, 05:39 PM
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"

boccuz
02-13-2018, 05:45 PM
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.)

Paul_Hossler
02-13-2018, 06:11 PM
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

boccuz
02-13-2018, 06:23 PM
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.


21617

boccuz
02-13-2018, 06:24 PM
How do you us using INDIRECT() and ROW() I have never used them prior

Paul_Hossler
02-13-2018, 06:32 PM
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

boccuz
02-13-2018, 06:38 PM
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!

Paul_Hossler
02-13-2018, 06:44 PM
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

boccuz
02-13-2018, 06:53 PM
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

boccuz
02-14-2018, 05:50 AM
S4 should be yes when Sheet 4, O32 >=1, but is returning No.

Paul_Hossler
02-14-2018, 07:15 AM
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_Hossler
02-14-2018, 07:23 AM
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

boccuz
02-14-2018, 07:55 AM
wow.. Thanks. So I just copy the cell down to row 50?

Paul_Hossler
02-14-2018, 08:17 AM
wow.. Thanks. So I just copy the cell down to row 50?

Should be all that's needed

The important thing is Application.Caller witch is the cell that the formula is in

Once we have that, we know the .Row and that allows us to go to the correct worksheet

Paul_Hossler
02-14-2018, 08:39 AM
If that works out, I noticed that it looks like you could have 100 buttons in A and B on "1", with the only difference being the virtually identical 1 of 100 macros

Maybe using a worksheet event hander to process a double click in A4, B4, A5, B5, .... would be easier



Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim r As Long, c As Long

With Target.Cells(1, 1)
r = .Row
c = .Column
End With

If r < 4 Or r > 50 Then Exit Sub
If Len(Me.Cells(r, 4).Value) = 0 Then Exit Sub

If c = 1 Then
Call Application.Goto(Worksheets(CStr(r)).Range("A49"), True)
ElseIf c = 2 Then
Call Application.Goto(Worksheets(CStr(r)).Range("A75"), True)
End If
End Sub



I accidently deleted the "Home" button macro

boccuz
02-14-2018, 09:49 AM
don't think I am copying the code correctly. I cannot get the formula to work in my working spreadsheet. I get ###### as a result.

I am going to Developer > Visual Basic > Module1_UDF > View Code then copy the code inside the box. not sure about about "Option Explicit" I don't see that. what am I missing?

boccuz
02-14-2018, 09:53 AM
actually, I am not seeing a Module1_UDF in my working spreadsheet. Only see it when your attachment is opened.

boccuz
02-14-2018, 09:58 AM
a lot of those macros are broken. I tried deleting, but couldn't. the delete button was grayed out.

Paul_Hossler
02-14-2018, 05:01 PM
actually, I am not seeing a Module1_UDF in my working spreadsheet. Only see it when your attachment is opened.

I inserted a new Standard Module, named it Module1_UDF, and wrote the macro on that

21625

boccuz
02-15-2018, 02:35 PM
Don't know what i am doing wrong. I copied in my working spreadsheet and am getting false regardless of argument. I did the same test in my sample spreadsheet and it worked. copied to both spreadsheet exactly the same

boccuz
02-15-2018, 02:38 PM
would you be willing to meet with me over skype and I will share my screen to show you what i am seeing? The file size of the working spreadsheet is too large for me to attach.

Paul_Hossler
02-15-2018, 05:32 PM
easier to just attach a workbook with "1", "4", and "5" again to the forum

Include the version of the macro you're having issues with

It works with ver 3 -- basically the same as ver 2 --

Do you have calculation set to automatic?

boccuz
02-16-2018, 08:47 AM
Here is the sample spreadsheet.

I copied to earnedmimimum code to module 1.





21628

boccuz
02-16-2018, 08:47 AM
yes, calculations is set to auto.

Paul_Hossler
02-16-2018, 09:04 AM
When you put the function in, you didn't remove the formula, so it was probably testing something like (formula) = (function) as a Boolean, and since they didn't match the answer was False

21631

Try ver 5

boccuz
02-16-2018, 09:19 AM
jeez... thanks. working now. I truly appreciate your help on this. Thank you very much!