How to insert =IF () - Formula result is a negative
Hello all,
can someone help me fix this formula so that the result is zero or higher? i.e. no negative results.
=([Delivery to Site]@row - [Warehouse Storage Starts]@row) * 20
The formula calculates cargo unpack date versus delivery date, plus free storage time, then adds storage cost.
I've tried to add a IF statement but keep getting #UNPARSEABLE
Thanks
Best Answer
-
Sorry, I forgot the leading square bracket in Delivery to Site
=IF([Delivery to site]@row - [Warehouse Storage Starts]@row > 0, ([Delivery to site]@row - [Warehouse Storage Starts]@row) * 20, 0)
Answers
-
Hey @David Bulmer
=IF(Delivery to Site]@row - [Warehouse Storage Starts]@row>0, ([Delivery to Site]@row - [Warehouse Storage Starts]@row) * 20, 0)
Does this work for you?
Kelly
-
Thanks Kelly, but I'm still getting the same error.
-
Sorry, I forgot the leading square bracket in Delivery to Site
=IF([Delivery to site]@row - [Warehouse Storage Starts]@row > 0, ([Delivery to site]@row - [Warehouse Storage Starts]@row) * 20, 0)
-
There are quite a few ways of doing this. My personal take would be:
=MAX(0, ([Delivery to Site]@row - [Warehouse Storage Starts]@row) * 20)
You could also use
=IF([Delivery to Site]@row >= [Warehouse Storage Starts]@row, ([Delivery to Site]@row - [Warehouse Storage Starts]@row) * 20, 0)
-
Thank you all for your input. This formula is now working!
=IF([Delivery to Site]@row - [Warehouse Storage Starts]@row > 0, ([Delivery to Site]@row - [Warehouse Storage Starts]@row) * 20, 0)
My next challenge with this formula is; if delivery date to site is blank, use today's date, so that a live calculation of storage fees is displayed. Any ideas for this?
-
Hey David
Does this work for you?
=IF(ISDATE([Delivery to Site]@row), IF([Delivery to Site]@row - [Warehouse Storage Starts]@row > 0, ([Delivery to Site]@row - [Warehouse Storage Starts]@row) * 20, 0), IF(TODAY() - [Warehouse Storage Starts]@row > 0, (TODAY() - [Warehouse Storage Starts]@row) * 20, 0))
Kelly
-
This would be my suggestion:
=IF(IF([Delivery to Site]@row <> "", [Delivery to Site]@row, TODAY()) - [Warehouse Storage Starts]@row > 0, (IF([Delivery to Site]@row <> "", [Delivery to Site]@row, TODAY()) - [Warehouse Storage Starts]@row) * 20, 0)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!