Making #Value not display so totals will sum
Posted by Chris Case on June 19, 2001 10:11 PM
I am using Vlookups in my spreadsheet. It is for a calculating cost. If I don't fill in a part number, #VALUE is displayed across all columns, and the sum function won't work. Is there a way to get rid of the #value with out deleting the formula in each cell?
Posted by Aladin Akyurek on June 19, 2001 10:32 PM
One of the possible causes is the folowing according to the Excel's Help:
Entering text when the formula requires a number or a logical value, such as TRUE or FALSE. Microsoft Excel cannot translate the text into the correct data type.
Help contains a suggested action:
Make sure the formula or function is correct for the required operand or argument, and that the cells that are referenced by the formula contain valid values. For example, if cell A5 contains a number and cell A6 contains the text "Not available", the formula =A5+A6 will return the error #VALUE!. Use the SUM worksheet function in the formula as follows to add the two values (the SUM function ignores text):
=SUM(A5:A6)
I'd suggest that you check whether you feed your VLOOKUP formula with right type of lookup value.
You can still sum a set of values containing errors such as #VALUE!.
=SUMIF(A1:A3,"<>#VALUE!",A1:A3)
Aladin
Posted by Sean on June 20, 2001 4:50 AM
========
Chris,
The best way to eliminate this is to test the Vlookup before you use it...ie:
=IF(ISERROR(VLOOKUP$B1,Info,2,FALSE)),"",VLOOKUP($B1,Info,8,FALSE))
The 1st part tests the existance of the lookup value (In my model I use ISNA rather than ISERROR but since I do not know what is causing your Value I am generalising)
If you lookup exists then It picks up the relevant values.
Hope this helps
Sean
Posted by Aladin Akyurek on June 20, 2001 5:01 AM
ISERROR
Sean,
Checking for #N/A is, say, OK. Concealing (other) truly problematic errors with ISERROR means keeping the trouble and not taking the effort for preventing them. That's one reason I avoided proposing it.
Regards.
Aladin
==============
