Subtraction formula with conditional IF statement from cells populated from index match

Hi everyone,

I'm trying to create an inventory spreadsheet that auto calculates the QTY in the warehouse by subtracting the amount received from the amount signed out. These columns are populated via an index match formula cross referencing another sheet. I also need there to be the caveat that if nothing has been signed out, thus rendering the QTY signed out column to say #NO MATCH, that it is represented in the equation as a 0 so the QTY in warehouse column matches the QTY received.

When I use the formula generator, the output translation reads logically, but the number in the sheet is -1 when 1-1 should be represented as 0.

Code generated and pulling the incorrect math is =IFERROR([QTY Received]@row - [QTY Signed Out]@row, 0 - [QTY Received]@row)

Any clue how to fix?

Find more posts tagged with

Formulas

inventory management

Accepted answers

All comments

MMaydel

Another option I tried is this: =IF(ISNUMBER([QTY Signed Out]@row), [QTY Received]@row - [QTY Signed Out]@row, [QTY Received]@row)

Which the output logic reads correctly, but now its returning no match. I assume I need to have the cells for QTY Signed out and QTY Received converted to value since they're text/number fields, but I'm unsure where to put that?

Brent Wilson

=IFERROR([QTY Received]@row - [QTY Signed Out]@row, [QTY Received]@row)

I don't know why you have 0-Qty Recieved

As 0-1=-1 that's where the issue comes from

And you should not be using an error as part of the standard code.

If you put the iferror in the formula for the signed out and return zero

Then it would not have to have the error check here as it would be 6-0=6

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.