Fix for #VALUE! error

P

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
 
J

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
 
Top