#Value Error

R

RayG

Hi
I'm using this formula
=IF(COUNTA(G2:H2)=2,INDEX(Calculations!$A$29:$P$45,MATCH(G2,Calculations!$A$29:$A$45,0),MATCH(H2,Calculations!$A$29:$P$29,0)),"")*E2
and coping it down a column and as long as column E is not null it returns
the value and if column E is null I get the #VALUE Error. Can this be told to
return blank if colunm E has a null vallue? The formula is written in column
I.
 
I

IC

RayG said:
=IF(COUNTA(G2:H2)=2,INDEX(Calculations!$A$29:$P$45,MATCH(G2,Calculations!$A$
29:$A$45,0),MATCH(H2,Calculations!$A$29:$P$29,0)),"")*E2

if column E is null I get the #VALUE Error. Can this be told to
return blank if colunm E has a null vallue? The formula is written in
Click to expand...
column I

Untested, but try

=IF(E2="","",(COUNTA(G2:H2)=2,INDEX(Calculations!$A$29:$P$45,MATCH(G2,Calcul
ations!$A$29:$A$45,0),MATCH(H2,Calculations!$A$29:$P$29,0)),"")*E2)

Ian
 
D

Dave Peterson

I think IC forgot the if:

=IF(E2="","",IF(COUNTA(G2:H2)=2,INDEX(calculations!$A$29:$P$45,
MATCH(G2,calculations!$A$29:$A$45,0),
MATCH(H2,calculations!$A$29:$P$29,0)),"")*E2)
 
I

IC

Dave Peterson said:
I think IC forgot the if:

=IF(E2="","",IF(COUNTA(G2:H2)=2,INDEX(calculations!$A$29:$P$45,
MATCH(G2,calculations!$A$29:$A$45,0),
MATCH(H2,calculations!$A$29:$P$29,0)),"")*E2)
Click to expand...

Oops! Sorry :)

Ian
 
Top