Fix for #VALUE! error

[Pat]

=IF(ISNA(MATCH(CC!$C106,PF05!$BM$24:$BM$1000,0)),"",IF(INDEX(PF05!Y$24:Y$100
0,MATCH(CC!$C106,PF05!$BM$24:$BM$1000,0))="UpdateCC",INDEX(PF05!$AP$24:$AP$1
000,MATCH(CC!$C106,PF05!$BM$24:$BM$1000,0)),""))

The above formula is in H106


When the following formula includes H106 in the formula an the error #VALUE!
will occur if there is no value to return in H106

=$H106+$BI106+$AS106+$AT106+$AU106-$BA106-INDIRECT("IV" & ROW())

Question: should the 1st or 2nd formula be changed to prevent the error?

Thankyou
Pat

[Jerry W. Lewis]

"" is a character string that cannot be coerced into a number, thus
addition with it is undefined.

=SUM($H106,$BI106,$AS106,$AT106,$AU106)-$BA106-INDIRECT("IV" & ROW())

will ignore non-numeric values in H106, BI106, AS106, AT106, or AU106

Jerry

Pat wrote:

> =IF(ISNA(MATCH(CC!$C106,PF05!$BM$24:$BM$1000,0)),"",IF(INDEX(PF05!Y$24:Y$100
> 0,MATCH(CC!$C106,PF05!$BM$24:$BM$1000,0))="UpdateCC",INDEX(PF05!$AP$24:$AP$1
> 000,MATCH(CC!$C106,PF05!$BM$24:$BM$1000,0)),""))
>
> The above formula is in H106
>
>
> When the following formula includes H106 in the formula an the error #VALUE!
> will occur if there is no value to return in H106
>
> =$H106+$BI106+$AS106+$AT106+$AU106-$BA106-INDIRECT("IV" & ROW())
>
> Question: should the 1st or 2nd formula be changed to prevent the error?
>
> Thankyou
> Pat