 # Help with an Excel formula (hard)

So my manager and I have been working on this huge formula that we just couldn’t get to work. We have finally narrowed the problem down to the “or” formula - it continues to display an error (#N/A). Below is a little pretend spreadsheet:

A B C
1 1 1
2 2 2
3 3 3
@ 4 0
5 5 5
6 6 6
4 =IF(OR(ISERROR(VLOOKUP(A8,‘Sheet2’!A1:A6,1.0,false)),VLOOKUP(A8,A1:A6,2.0,false)=0.0),“TRUE”,“FALSE”)

So there are columns A, B & C, and a total of 6 rows. I have made the cell value the same as the row number, with the exception of “cell” A8, which has a value of 4. “Cell” A4 is supposed to be blank, but it doesn’t display right on the board if I do it that way, so @ will have to suffice.

I believe the formula works on columns B & C. From what I understand though, it should not return #N/A because of the blank. Am I wrong? Does this need to be set up differently?

There are several sources of error in your equation. A correct version would be:

=IF(IF(ISERROR(VLOOKUP(A8,A1:A6,1,FALSE)), “TRUE”, IF(VLOOKUP(A8,A1:A6,1,FALSE)=0, “TRUE”, “FALSE”)),“TRUE”,“FALSE” )

Also, blank spaces cause a #N/A to be returned by VLOOKUP so you will have to correct that unless you want a blank space to cause a #N/A exception.

Another thing to note is that the OR function in Excel isn’t very good. It will actually try to evaluate each condition in the OR statement even if the first condition is true. Hence, the usage of IF statements in my above equation.

Thank you so much!

We were talking about this in a MatLab training course I had a couple weeks ago. Do you (or somebody) know the command in MatLab (or another language) so that it won’t do that? Or is it only something you can do with an optimization add on package?

I have used MatLab before but I don’t know offhand how to keep MatLab from evaluating every term in an OR statement even when it finds a true condition. I don’t have a copy of MatLab so I can’t help you there.

I do program a lot and most programming languages will stop evaluating an OR condition after it finds the first true one, which is the proper and efficient way to implement OR. I find it surprising and puzzling why Excel and MatLab would do it the inefficient way on such a basic function.

A work around would be to code your own OR function in a separate file and call that special OR in place of all your regular OR’s. This should be pretty easy to code.

Your welcome. And I just realized that you don’t need the outer if statement in my solution. The simplified version:

=IF(ISERROR(VLOOKUP(A8,A1:A6,1,FALSE)), “TRUE”, IF(VLOOKUP(A8,A1:A6,1,FALSE)=0, “TRUE”, “FALSE”))

Thanks for the reply. It has its own command, something simple that I would never think of on my own. Kind of like the .* command to multiply two matrices element wise instead of by using matrix multiplication. I’ll look into it tomorrow when I’m at my work computer and see if I can figure out what I’m talking about.

The key to figuring sh*t like this out is this:

If you select a part of a formula in the Formula Bar and press Command = on the Mac, or Control = on the PC, it will calculate just that part of the formula.

So in your example, you might first just select the part that says

ISERROR(VLOOKUP(A8,‘Sheet2’!A1:A6,1.0,false))

and press Command =. It will evaluate just that part of the overall expression, and replace it with “TRUE” or “FALSE”. Or perhaps with #N/A or some other error.

By investigating and evaluating the sub-formulas that make up the whole formula, you can determine which one of them is causing the error.

Remember to use the “Esc” key to get out of the formula bar when you are done, so it returns to the original formula.

Finally, you have to select a logically complete subsection of the overall formula in the formula bar. Suppose the formula is

=32SIN(C3)

Excel will evaluate it if you select 32, or SIN(C3). It will not evaluate 32* (extra multiplication sign), or SIN(C3 (missing parenthesis).

Good hunting,

w.

Awesome, that is very helpful! I will definitely be passing this one around!

Thanks for the acknowledgement, liirogue, I wasn’t sure if anyone had picked up on it and it has been a valuable tool for me.

w.

I just checked in Matlab, and if you use OR (i.e. ‘|’) and if the first argument is true, it does not evaluate the second.