1

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.

3
  • Look into IFERROR().
    – Kyle
    Oct 19 '15 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.
    – teylyn
    Oct 19 '15 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!
    – Mac Sigler
    Oct 20 '15 at 17:53
1

I used Kyle's suggestion of using IFERROR() to help clean up the formulae and keep them readable.

It seems like newer versions of Excel have an IFNA() function, but that's currently unavailable in Google Sheets. Hopefully it will be implemented in the future.

It also goes without saying that IFERROR() will swallow any errors thrown, so be careful if your data requires special error handling.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.