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!

Find more posts tagged with

Accepted answers

All comments

Kelly Moore

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

NWSpur

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!

Kelly Moore

Phew! Glad that worked.

Kelly

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.