Thread: Hide/eliminate #Value error

Hi, I am doing a simple subtraction, i.e. =E21-G21.

I am subtracting two Vlookup vormulas that looks like this

=IF(COUNTIF($I$1:$I$83,"IC Prov   ")=0,"",INDEX($W$1:$W$83,SMALL(IF($I$1:$I$83="IC Prov   ",ROW($I$1:$I$83)-ROW($I$1)+1),MIN(2,COUNTIF($I$1:$I$83,"IC Prov   ")))))

&

=IF(COUNTIF($I$1:$I$83,"IC Prov   ")=0,"",INDEX($W$1:$W$83,SMALL(IF($I$1:$I$83="IC Prov   ",ROW($I$1:$I$83)-ROW($I$1)+1),MIN(1,COUNTIF($I$1:$I$83,"IC Prov   ")))))

I am getting a #Value error if they both return a value = "".

How do I fix it? the IF() command does not work here.

Validate both contain numbers prior to coercing

=IF(COUNT(E21,G21)<2,"n/a",E21-G21)

or, given use of XL2007

=IFERROR(E21-G21,"n/a")

modify n/a per preference

(obviously in your OP the formulae are identical...ie result of subtraction would only ever be #VALUE! or 0)