#Value Error

[Guest]

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.

 

[IC]

=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

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

 

[Guest]

received error message (the formula you typed contains an error

 

[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)

 

[IC]

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)

Oops! Sorry

Ian