Solved Windows Excel 10 IF AND function broken but really simple

September 28, 2012 at 07:38:40
Specs: Windows 7
I have the following formula that is returning the words TRUE or FALSE.

=IF(AND(L6=1, R6=7),1,0)

If L6 equals 1 and R6 equals 7 shouldn't this formula return 1 in the cell? I get TRUE
If L6 equals 1 and R6 equals 5 shouldn't this formula return 0 in the cell? I get FALSE
If L6 equals 0 and R6 equals either 7 or 5 shouldn't this formula return 0 in the cell? I get FALSE.

It seems so simple. What am I missing?

You help is really appreciated.

Terry


See More: Windows Excel 10 IF AND function broken but really simple

Report •

#1
September 28, 2012 at 08:15:47
I'm not sure why, perhaps the way the cell is formatted(?) but it seems that Excel is converting your 1 and 0 to logical 1 and 0, therefore returning TRUE or FALSE.

That does not happen when I paste your formula into my spreadsheet.

You could try this, but I don't see why you would have to:

=IF(AND(L6=1, R6=7),"1","0")

This forces a text 1 or 0 in the cell. Depending on what you are doing with the 1 and 0, Excel may or may not consider the text value to be a number. You'll have to try it and see.

If it doesn't like it, you could try this to force a number, but again, you shouldn't have to.

=IF(AND(L6=1, R6=7),"1"*1,"0"*1)
or
=IF(AND(L6=1, R6=7),1*1,0*1) (untested, since I can reproduce the problem)

I'm just starting to use 2010 on a daily basis, so I don't know if there is a setting that would force 1's and 0's to be considered logical values. I've never heard of it.

Have you tried this formula in a different workbook?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
September 28, 2012 at 08:46:16
I tried all three and it doesn't work. However, I did open a new workbook, copied and pasted the cell from the old workbook and it worked fine.

I went back to the original cell and reformatted the cell to Number with zero decimal places (was General) and it still didn't return a number.

This is a very complicated and involved workbook with 12 sheets and I need this formula in about 20 cells in each of those sheets. Should I repost this problem separately?

At least I know what the other problem is and my original formula was, indeed, correct. For that I thank you.

Terry


Report •

#3
September 30, 2012 at 05:29:05
✔ Best Answer
If the forumlua works in other spreadsheets, it's possible that the original sheet is corrupted. It's really hard to say. I've seen single cells in worksheets become corrupted and nothing will fix them.

Can you copy the sections that work to other spreadsheets without copying the cells that are giving you the problem and rebuild your spreadsheets that way?

Without actually having the spreadsheets to work with, I don't know what else to offer.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
September 30, 2012 at 07:50:01
I've put your issue in front of some resources that I have access to. I'll let you know if I hear anything.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question