Wrong Result with an IF function

[Thij_rahya]

This is odd:

=IF(LEN(VLOOKUP($A4,SAMPLE!$A$9:F201,6,FALSE))=0,J4,T(INDIRECT("$M$"&((CELL("ROW")-3)+(LEN(VLOOKUP($A4,SAMPLE!$A$9:F201,6,FALSE))+2)/5))))

Let me bread it down
"=IF(LEN(VLOOKUP($A4,SAMPLE!$A$9:F201,6,FALSE))=0" is FASE

Skip, "J4" for the "False" part of the equation
"T(INDIRECT("$M$"&((CELL("ROW")-3)+(LEN(VLOOKUP($A4,SAMPLE!$A$9:F201,6,FALSE))+2)/5))))" comes to equal "Red, White"

when I run the formula evaluation it comes down to the last step where it says

"IF(FALSE,#N/A,"Red,White")" and when I click on Evaluate it gives me a
completely random color. In this case, it's Red. in other places where the
answer should be Classic Navy, and the formula says "IF(FALSE,#N/A,"Classic
Navy")" it will still come up "Red" or "Blue" or some random color.

I have noticed that if I change the grammar of the formula it will correct
that one cell, but when I fix all the other cells to a similar grammar, it
breaks again.

Is there a work around for this, or, because I am using cell references that
contain other formulas and this is quite a large spreadsheet, am I just
overtaxing Excel's abilities?

 

[Niek Otten]

It seems to me that ypu don't give us all too precise information:

<Skip, "J4" for the "False" part of the equation>

You mean the "True" part?

<it gives me a completely random color>

What do you mean? Certainly Excel is not inventing words that mean colors,
does it? What do you get in the cell, literally?

< if I change the grammar of the formula>

What exactly does that mean?

< it will correct that one cell>

So, what does Excel do ** exactly**?

 

[Thij_rahya]

Hey Niek,

Please find your answers below.

It seems to me that ypu don't give us all too precise information:

<Skip, "J4" for the "False" part of the equation>

IF(logical_test,value_if_true,value_if_false)... J4 is the "Value_if_true"
and I was suggesting that we could skip this, because
IF(LEN(VLOOKUP($A4,SAMPLE!$A$9:F201,6,FALSE))=0 is Fale, so we can Skip the
"Value_if_true" and we canmvoe on the the "Value_if_False"

You mean the "True" part?

"Value_is_False" part is running to the end of the evaluation and everything
is correct with the formula in the Formula Evlauation, until...

<it gives me a completely random color>

It tends to give me a color, which seems to be the first color in the first
cell that looks up a color in the entire document. If this is red, then all
of them will be red. If it is blue, then they will all be blue.

All of the formulas will calculate correctly if I calculate them
individually, however, when I calculate the entire page, I end up with the
wrong answer in practically every one of these cells

< if I change the grammar of the formula>

What exactly does that mean?

Changing the grammar of the formula:
=IF(C2<>0,B2,D2)
=IF(C2=0,D2,B2)
Just changing the formual around so that it works out simillarly, but with
opposite results, much like changing grammar in language.

 

[Niek Otten]

I still don't get it.
What do you mean if you say a formula returns a color? Is the cell colored?
Do you get the name of a color in the cell, like "Blue"? Are there lists of
colors in your workbook?

Let's forget about "the whole page", just give three formulas, the values of
the input cells, the formulas and the results you get.

BTW do you have Iteration checked (Tools>Options>Calculation tab)

Which version of Excel?

 

[Harlan Grove]

This is odd:

=IF(LEN(VLOOKUP($A4,SAMPLE!$A$9:F201,6,FALSE))=0,J4,
T(INDIRECT("$M$"&((CELL("ROW")-3)
+(LEN(VLOOKUP($A4,SAMPLE!$A$9:F201,6,FALSE))+2)/5))))

....

Your problem is caused by using CELL("ROW"). When you use the Formula
Evaluate tool, your active cell must be the cell containing the
formula, so CELL("ROW") would evaluate to the row number of the
formula's cell. However, when recalculating CELL("ROW") will evaluate
to the active cell's row number. For example, if this formula were in
X99 but G237 were the active cell, CELL("ROW") would evaluate to 237
rather than 99.

Try replacing the False term by

T(INDEX($M:$M,ROW()-3+LEN(VLOOKUP($A4,SAMPLE!$A$9:F201,6,0))+2)/5))

which uses ROW() rather than CELL("ROW"), where ROW() returns the
formula's cell's row number. This also eliminates the unnecessary
volatile INDIRECT call.

I'd also note that your lookup table has its top-left corner fixed but
its bottom-right corner floating. If you're using several of these
formulas copied/pasted or filled into different cells, leaving the
bottom-right corner floating is almost certainly a mistake.

 

[Thij_rahya]

Initial Results looking good, corrections being made and...

 

[Thij_rahya]

.... We are golden! Thanks a ton, Harlan!

Initial Results looking good, corrections being made and...