# How to simplify Excel/Google Sheets formula with redundant method calls?

I have an Excel (Well, technically Google Sheets) workbook containing product inventory warehouse storage information. I figured out how to construct queries so it grabs the needed information from one sheet with multiple entries (think "stream of consciousness") and puts it into another sheet where it only lists the latest item for a particular product position.

The issue I was having was that if no item was found with that description, it would give me an #N/A entry instead of leaving the cell blank. Research led me to wrap the query in a isna() call so I could manually blank out any #N/A cells, but now the query is called twice for every item, noticeably slowing down the computation.

Example:

A = Timestamp B = Aisle C = Section D = Row E = Position F = Quantity G = Description

=if(isna(QUERY('Form Responses 1'!\$A\$2:\$G\$10011, "select A where (B = "&B2&" AND C = "&C2&" AND D = '"&D2&"' AND E = '"&E2&"') ORDER BY A DESC LIMIT 1")), " ", QUERY('Form Responses 1'!\$A\$2:\$G\$10011, "select A where (B = "&B2&" AND C = "&C2&" AND D = '"&D2&"' AND E = '"&E2&"') ORDER BY A DESC LIMIT 1"))

My questions are:

1) Is there a more efficient way to blank out #N/A cells?

2) If not, is there a way I can avoid calling the same query twice (e.g. with something like a temporary variable within the formula)?

Any suggestions to "beautify" my formulae are welcome and appreciated.

• Look into IFERROR().
– Kyle
Oct 19, 2015 at 21:20
• Your file is either a Google spreadsheet or an Excel spreadsheet. "Technically" does not come into it. The two are not the same and formulas differ. Tag your question accordingly. Oct 19, 2015 at 21:56
• @Kyle It looks like new versions of Excel might have an IFNA() function available, but until that becomes a widespread standard this looks like the best option for keeping my formulae clean. Thanks! Oct 20, 2015 at 17:53