Prevent #VALUE error

[Ducatisto]

I have tried but ultimately failed at trying to prevent the #VALUE error because cells contain no values. How can I have a cell contain a zero value so enabling a sum?

This is my formula for the value to be entered in the cell immediately above;
This is in C10, =IF(C9<>"",HLOOKUP(C9,KPI!$C$5:$F$6,2),"") Obviously if no data has been entered it throws up the #VALUE error but how can I have the cell with the formula in return a zero value prior to data entry in the cell above?

I do hope that makes sense, thank you in advance.

Duca

[royUK]

You can use ISERROR, but if there are many of such formulas it can slow calculation. Alternatively use Conditional Formatting to hide the error, see

http://www.excel-it.com/excel_condit...formatting.htm

[Ducatisto]

Thank you Roy but due to my lack or experience I was failing to type the correct formula. I do have the formula for counting all of the returned values in which the error is getting returned;
=SUMPRODUCT((MOD(ROW(C9:C112),2)=0)*(C9:C112))

If I could replace it with a formula that counts every other cell (I need all even cells counted from C10 to C112 inclusive) and ignore blanks, that too would benefit me. Is that an easier solution?

Thank you.

Duca

[daddylonglegs]

=SUMPRODUCT((MOD(ROW(C9:C112),2)=0)*(C9:C112))

Hello Duca,

If C9, or any other cell in C9:C112, contains a "formula blank" [i.e. "" returned by a formula] then your SUMPRODUCT formula will give an error, change it to this:

=SUMPRODUCT((MOD(ROW(C9:C112),2)=0)+0,C9:C112)

Note: although that formula should work, it isn't very robust. If you add a row at the top of the worksheet it'll give the wrong result, I'd advise you to further change to

=SUMPRODUCT((MOD(ROW(C9:C112)-ROW(C9)+1,2)=0)+0,C9:C112)

[Ducatisto]

Thank you, in the meantime I realised my error in the formula in my first post

=IF(C9<>"",HLOOKUP(C9,KPI!$C$5:$F$6,2),"")

It should have been this;

=IF(C9<>"0",HLOOKUP(C9,KPI!$C$5:$F$6,2),"0") I had missed out the zero's, doh!

In your mind, do you think it would be quicker and more efficient either way or would I not notice any performance hit?

Thank you all for your help.

Duca