Formula Working In Some Cells but Not Others - No errors
I am using this formula for pro-rating costs over months of particular projects. It works up until December 2023, but as soon as I enter the formula for 2024 months, it displays a blank cell, no errors. Just blank.
=IFERROR(IF(PRORATE(Fee@row, Start@row, Finish@row, [Jan-24 Begin]@row, [Jan-24 End]@row) = 0, "-", PRORATE(Fee@row, Start@row, Finish@row, [Jan-24 Begin]@row, [Jan-24 End]@row)), "")
It's very frustrating being that I am using the exact same formula in many other cells and it's working!
Answers
-
Hey @NWSpur
Let's start troubleshooting your formula. Just while we are testing, remove your IFERROR function - it seems to be doing it's job! This will help us see what errors, if any, you are getting.
Without the IFERROR function your formula will look like this:
=IF(PRORATE(Fee@row, Start@row, Finish@row, [Jan-24 Begin]@row, [Jan-24 End]@row) = 0, "-", PRORATE(Fee@row, Start@row, Finish@row, [Jan-24 Begin]@row, [Jan-24 End]@row))@NWSpur
I was able to immediately replicate an error when I changed the column type of [Jan-24 Begin] from a Date column to a Text/Number column. This would show up as an Invalid Data Type error. Verify the Mon-24 columns are all Date formatted columns.
When you look at your formula in the sheet, does it show as colored text? If not, delete your [Mon-24] references, one by one, from the formula and reinsert, one by one, by clicking into the appropriate cell. When typing out names of columns manually it is easy to have an inadvertent extra space, which can be very difficult to spot. This would show up as Unparseable.
Let me know what you find, and, if the above doesn't take care of it, tell us what error you are seeing. Screenshots of formulas always give the community a lot of information when we are troubleshooting.
Kelly
-
Thank you for your response Kelly! The date columns indeed were formatted as text, so I changed it to Date and voila! I knew it was an easy fix, but working on it all day blinded me of the simple solution. Thanks again!
-
Phew! Glad that worked.
Kelly
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 202 Industry Talk
- 430 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!