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?
Answers
-
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? -
=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
Brent C. Wilson, P.Eng, PMP, Prince2
Facilityy Professional Services Inc.
http://www.facilityy.com
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 437 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!