N/A values

[jturn]

I've created a spreadsheet with the help of this site (very greatful) for our restaurant's catering business. Within this spreadsheet I have 3 worksheets. The first is called "menu items" and its a list of the those items that we sell as well as the unit cost. The second is "Information". In this worksheet we enter all the information for the catering event including a table that has drop down list of the menu items created and the vlookup function to carry over the appropriate unit cost for the item selected. The third worksheet is a preformated invoice. In this one, cells are linked from the "Information" sheet to fill in the correct informtion. The problem that I'm having is the table in the Invoice page returns "N/A" because no items (or not enough) items are selected for those particular cells. Therefore it won't calculate a sum because of the "N/A" in the column. Is there a way for it to return a blank value if no items are selected for that cell or is there a better way to set this up.

thanks,
Jturn

 

[Joe4]

If you want to do a sum, return a zero in cases of an "#N/A" error, using this kind of layout:

=IF(ISNA(VLOOKUP(...)),0,VLOOKUP(...))
where
VLOOKUP(...) is your current VLOOKUP formula.

 

[jmthompson]

 

[Aladin Akyurek]

I've created a spreadsheet with the help of this site (very greatful) for our restaurant's catering business. Within this spreadsheet I have 3 worksheets. The first is called "menu items" and its a list of the those items that we sell as well as the unit cost. The second is "Information". In this worksheet we enter all the information for the catering event including a table that has drop down list of the menu items created and the vlookup function to carry over the appropriate unit cost for the item selected. The third worksheet is a preformated invoice. In this one, cells are linked from the "Information" sheet to fill in the correct informtion. The problem that I'm having is the table in the Invoice page returns "N/A" because no items (or not enough) items are selected for those particular cells. Therefore it won't calculate a sum because of the "N/A" in the column. Is there a way for it to return a blank value if no items are selected for that cell or is there a better way to set this up.

thanks,
Jturn

You could invoke a SumIf formula in order to total a range with #N/A's...

=SUM(SUMIF(Range,{"<0",">0"}))