driller said:
thanks for reply,
i tried as u suggested and found again this part of the formula
=exact($C$1:$E$3,F6) result is #value!
i format tested the X1, Y1, Z1 on C3:E3 and F6:H6 ,
copy paste same format
and value as general and test it all as text...
i am confused now..with sumproduct giving a result of 1 again.
Does sumproduct read the #value! as 1 under any formulation.
First, trust the EXACT formula given earlier worked ok for you, right ?
Please confirm this
Ok, as regards your dissection observations above,
this is the correct way to observe the evaluation process
Let's take this part of the formula in B6:
SUMPRODUCT(--(EXACT($C$1:$E$3,F6)))
(with X1, y1, Z1 entered in C3:E3)
In the formula bar for B6, carefully select only the part:
EXACT($C$1:$E$3,F6)
then press F9 key to evaluate
You would see that it evaluates to a series of FALSEs / TRUEs, ie:
{FALSE,FALSE,FALSE;FALSE,FALSE,FALSE;TRUE,FALSE,FALSE}
Press Esc key to revert, now carefully select only the part:
--(EXACT($C$1:$E$3,F6))
then press F9 again
With the double unary wrapped around, viz: --(EXACT(...)),
the series of FALSEs / TRUEs returned by EXACT will be coerced (evaluated)
to a series of 0s / 1s, ie the results will appear as:
{0,0,0;0,0,0;1,0,0}
(FALSE =0, TRUE = 1)
Press Esc again to revert, now select only the part:
SUMPRODUCT(--(EXACT($C$1:$E$3,F6)))
then press F9 again
The SUMPRODUCT(...) will evaluate the series of 0s / 1s, viz:
{0,0,0;0,0,0;1,0,0}
to return the final result of: 1
If we place in a cell and just press ENTER:
=EXACT($C$1:$E$3,F6)
we'd get the wrong result of: #VALUE! because the expression is an array and
needs to be array-entered by pressing CTRL+SHIFT+ENTER.
If we array-enter it correctly, ie: =EXACT($C$1:$E$3,F6)
then the expression will evaluate correctly, *but* the cell will display
only the leftmost value in the array returned, ie all we'd see in the cell
is: FALSE
In order to see the entire array returned, we'd need to select the
array-entered expression in the formula bar, viz select:
{=EXACT($C$1:$E$3,F6)}
then press F9
Note: The curly braces: { } are auto-inserted by Excel upon correct
array-entering. We can use this as a visual check in the formula bar for any
array-entered formulas.
Pressing F9 will now reveal the entire array returned:
{FALSE,FALSE,FALSE;FALSE,FALSE,FALSE;TRUE,FALSE,FALSE}
Hope the above clarifies it a little better here ..
