Using AND function with formula result values

Question

This is not a specific VBA question, but a formula question. I have a sheet where I have two cells, g2 and j2 that have formulas in them. I want to write a formula that says 'if g2 and j2 are both 0, then....'

This seems like something that should be extremely easy, but every time I try it, the AND function yields a 'false' value. If I manually type in 0 into each cell, then it yields true. Which leads me to believe that the AND function cannot be used with formula result values but I cannot find explicit confirmation of this.

Can someone please confirm or deny this, and if that is the case, is there another way for me to do this? I already checked to see if I could add in some helper columns, or just copy/paste values but was told those are not options.

Here is my formula as it stands...when evaluating just the AND portion, it comes out as false. The returned values in the referenced cells are 0. Again, if I just hard code the numbers as 0, it works. =IF(AND(G2=0,J2=0),"0%",IF(ERROR.TYPE(J19/G19)<3,"100%"))

Thanks, so this one shows as false: =IF(OR(G2="",H2=""),"",IF(AND(G2=0,H2=0),"0",H2-G2)). This is in j2. However, the result of the formula is 0. — learningthisstuff
– learningthisstuff, Commented Nov 8, 2019 at 21:21
BigBen, weird thing too...when I add the round function, it yields true...but only if cell j2 has a number in it! Meaning, the result of the formula in j2 is 2500: =IF(OR(G2="",H2=""),"",IF(AND(G2=0,H2=0),"0",H2-G2)) =value of 2500. Now they both show as in the AND formula. When I remove the ROUND function, it goes back to false. — learningthisstuff
– learningthisstuff, Commented Nov 8, 2019 at 21:29
For that matter, don't return a 0% within quotes either. That's text, not a number. Same for 100%. — BigBen
– BigBen, Commented Nov 8, 2019 at 21:37

BigBen · Accepted Answer · 2019-11-08 23:33:37Z

1

Can someone please confirm or deny this, and if that is the case, is there another way for me to do this?

The only possible answer is that one or both of G2 and J2 are not equal to 0. To refute your hypothesis that AND doesn't work with formula values, a simple test:

1) In G2: =0, =1-1, =2^0-1/2-1/2 or some other formula that results in 0.

2) In J2: =0, =1-1, =2^0-1/2-1/2 or some other formula that results in 0.

3) In a new cell: =AND(G2=0, J2=0).

You may need to ROUND to some number of digits perhaps:

=AND(ROUND(G2,4)=0,ROUND(J2,4)=0).

EDIT: From your comments, it seems like this boils down to a simple misunderstanding.

The Takeaway: "0" does not equal 0.

"0%", "0", and "100%" are text when inside quotes, not numbers.

edited Nov 8, 2019 at 23:33

answered Nov 8, 2019 at 21:13

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using AND function with formula result values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related