the formula sees the cell with a value
 

hi all,
i used the following equation in Q:Q range and a similar one in P:P range:
=IF(LEN(INDEX(dewanless!O:O,MATCH(urban1!$F25,dewa nless!$E:$E,0)))=0,"",INDEX(dewanless!O:O,MATCH(ur ban1!$F25,dewanless!$E:$E,0)))
and in another range of cells :
=(1000*(SUMPRODUCT((Q2:Q25<"")*(P2:P25))))/(SUMPRODUCT((P2:P25<"")*(Q2:Q25)))
the error value appears if the result of the first equation is a blank cell
either in the Q range or the P range, what should i do?

the formula sees the cell with a value
 

This is a bit unruly, but you might want to add an error check to the formula
like this,

=if(iserror(IF(LEN(INDEX(dewanless!O:O,MATCH(urban 1!$F25,dewanless!$E:$E,0)))=0,"",INDEX(dewanless!O :O,MATCH(urban1!$F25,dewanless!$E:$E,0))),"",IF(LE N(INDEX(dewanless!O:O,MATCH(urban1!$F25,dewanless! $E:$E,0)))=0,"",INDEX(dewanless!O:O,MATCH(urban1!$ F25,dewanless!$E:$E,0))))

Please Note, I have not checked this specific formula, but if you would like
me to, send a sample data set.
--
Anne Murray


"nada" wrote:

hi all,
i used the following equation in Q:Q range and a similar one in P:P range:
=IF(LEN(INDEX(dewanless!O:O,MATCH(urban1!$F25,dewa nless!$E:$E,0)))=0,"",INDEX(dewanless!O:O,MATCH(ur ban1!$F25,dewanless!$E:$E,0)))
and in another range of cells :
=(1000*(SUMPRODUCT((Q2:Q25<"")*(P2:P25))))/(SUMPRODUCT((P2:P25<"")*(Q2:Q25)))
the error value appears if the result of the first equation is a blank cell
either in the Q range or the P range, what should i do?

the formula sees the cell with a value
 

thanks a lot.