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
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
Hope that helps.
RoyUK
--------
If you are pleased with a member's answer then use the Star icon to rate it, if you are pleased enough to part with cash consider a donation to Children in Need
For Excel Tips & Solutions, free examples and tutorials why not check out my downloads
New members please read & follow the Forum Rules
Remember to mark your questions Solved and rate the answer(s)
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
Hello Duca,Originally Posted by Ducatisto
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)
Last edited by daddylonglegs; 02-27-2008 at 08:28 AM.
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
There are currently 2 users browsing this thread. (0 members and 2 guests)
Posting Permissions
Reply With Quote
Bookmarks