Wrong Result with an IF function
 

This is odd:

=IF(LEN(VLOOKUP($A4,SAMPLE!$A$9:F201,6,FALSE))=0,J 4,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?

Wrong Result with an IF function
 

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**?


--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Thij_rahya" wrote in message
...
This is odd:

=IF(LEN(VLOOKUP($A4,SAMPLE!$A$9:F201,6,FALSE))=0,J 4,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?

Wrong Result with an IF function
 

Hey Niek,

Please find your answers below.

"Niek Otten" wrote:

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.

Kind regards,

Niek Otten
Microsoft MVP - Excel


"Thij_rahya" wrote in message
...
This is odd:

=IF(LEN(VLOOKUP($A4,SAMPLE!$A$9:F201,6,FALSE))=0,J 4,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?

Wrong Result with an IF function
 

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 (ToolsOptionsCalculation tab)

Which version of Excel?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Thij_rahya" wrote in message
...
Hey Niek,

Please find your answers below.

"Niek Otten" wrote:

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.

Kind regards,

Niek Otten
Microsoft MVP - Excel


"Thij_rahya" wrote in message
...
This is odd:

=IF(LEN(VLOOKUP($A4,SAMPLE!$A$9:F201,6,FALSE))=0,J 4,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?

Wrong Result with an IF function
 

Thij_rahya wrote...
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.

Wrong Result with an IF function
 

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

"Harlan Grove" wrote:

Thij_rahya wrote...
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.

Wrong Result with an IF function
 

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

"Thij_rahya" wrote:

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

"Harlan Grove" wrote:

Thij_rahya wrote...
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.