• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

=IF returning True when it is False

D

Divelonger

New Member
I am working with a Macro sheet and was having trouble with a formula so I stripped it down to a bare bones formula and it is still returning the wrong value.


=IF(M24>100,M24,"") returns TRUE even when M24 = 82.
 
Hi, Divelonger!

I could only simulate that assigning TRUE to M24.

Can you please copy/paste all involved cells/formulas and displayed values? Or update a sample file?

Regards!
 
Is Calculation on Automatic?

Press F9 does the result change?
 
Hi,


The closest answer I guess, is M24 in text format. When i tried formatting a cell to text and in next cell if put the same formula and the result is exactly what Divelonger is stating. So, I think changing the format of M24 to number format should solve.


I am curious to know the results and other suggestions, as I am also facing issues with data formats :(


Regards,

prasad Dn
 
I'm also curious as to Divelonger's "working with a Macro sheet"

Is he on a Excel 4 Macro sheet or a Code Module ?
 
The bigger picture is that I have data that is being pulled from a couple of workbooks into an Excel Mail Merge (using MACROS to do the mail merge). The formula below works when I test it on a worksheet, but will not work when the data cells are from other worksheet links.


A B FORMULAS

HAVE 99 =IF(AND(A1="HAVE")*(B1<100),B1,(IF(AND(A1="HAVE NOT")*(B1<140),B1,"")))

HAVE 140 =IF(AND(A2="HAVE")*(B2<100),B2,(IF(AND(A2="HAVE NOT")*(B2<140),B2,"")))

HAVE NOT 110 =IF(AND(A3="HAVE")*(B3<100),B3,(IF(AND(A3="HAVE NOT")*(B3<140),B3,"")))

HAVE NOT 150 =IF(AND(A4="HAVE")*(B4<100),B4,(IF(AND(A4="HAVE NOT")*(B4<140),B4,"")))
 
I thought so too, however, I did change the Have & Have NOT values to numeric values and the formula still did not work.
 
Hi, Divelonger!


Checking your data, I get this, which I think it's ok. Please confirm.

[pre]
Code: 
HAVE    	99	99
HAVE    	140
HAVE NOT	110	110
HAVE NOT	150
[/pre]
First and third conditions, true.


Regards!


PS: And what about the example of the first post?
 
Sir JB7, that is correct. Now if I take the formula and place it in the Macro worksheet, and change the A1 to the worksheet reference (M26) and B1 to M24 it does not work. BTW, on the original post I took out the M26=HAVE and HAVE NOT out and did a simple test of M24 and it still did not work. I think it has to do with the links, but I don't know what is causing the problem or how to fix it.
 
Hi, Divelonger!

Can you please upload the workbook? So we can go straight to the point where it doesn't work.

In the green sticky posts at the forum main page you'll find uploading instructions, if needed.

Regards!
 
Here is the link to the workbook:

https://docs.google.com/open?id=0BwqYK4FIK-daZjZjYTEzOTYtNzkwMy00YTIzLTgxZjAtNmMyNTIxYzRmM2Y0

Thanks.
 
Hi Divelonger,


There is a cell shift by +1 in your formulas in the data sheet.

for example the formula in O1 contains [Data Master.xlsx]OFFICE'!M2="Y";"HAVE";"HAVE NOT") Notice the M2. i think it should be M1 and all the cells have the same error. Please amend it and see if it works.
 
Hi Divelonger,


In your Form sheet, change cell D3 which is called RowIndex to 24. this is the number that matches M24 and thus the result will be 75.


Done
 
Hi, Divelonger!


I have several comments about the formula in sheet Form cell G24, as follows:


a) the formula displays the apparently -for me- correct value of 112, Data!U4=1, M24>100 (112), so I don't see where's the issue... if it displays a wrong value, let's analyze in detail the formula at b) and c)


b) IF instructions have the structure (test_condition, value_true, value_false)


c) as far as I can see you have:

1) test_condition: AND(INDIRECT("Data!U" &RowIndex)=1)*(M24>100)

AND instructions have the structure (test_value1, test_value2, ...) but here we only have one argument, not two or more

2) value_true: M24, in this case 112

3) value_false: (IF(AND(INDIRECT("Data!U" &amp,RowIndex)=2)*(M24&gt,140),M24,""))

Same considerations as previous.


So, going straight to the point and not guessing what should the formula is supposed to do, please explain how do you want to perform the calculations so as to try aiding you with the correct formula,


Regards!
 
You must log in or register to reply here.
Back
Top