Formula returning a negative number when a zero is expected.

Hi Smartsheet Community,

Probably something easy I'm missing but it's escaping me. I am looking to have the total number of work orders broken out into number of work orders to be completed per month during the contract start and end dates. Currently the formula is returning a negative number or a number in a month column outside of the contract dates. If I have 100 work orders and my contract is three months, I only want to see a work order break down for those three months, and totaling 100 work orders.

Simplification of the formula would be appreciated too if possible.

Here is an example of the formula for February that returns a result of -933 . For each month the "FEBEND#" is swapped for the correct month date reference. So MAREND, APREND, etc.

DummyData1.png

=IFERROR(IFERROR(IF(IF(AND([2022 Start Date]@row > JANSTART#, [2022 Start Date]@row < FEBEND#, [2022 End Date]@row <= FEBEND#), NETWORKDAY([2022 Start Date]@row, FEBEND#) * [Phases Daily Run Rate (Constant)]@row, 0) > [Systems to Be Completed Per Phase]@row, [Systems to Be Completed Per Phase]@row, NETWORKDAY([2022 Start Date]@row, FEBEND#) * [Phases Daily Run Rate (Constant)]@row), "") - [Jan Goal]@row, 0)

Find more posts tagged with

Accepted answers

Jeff Reisman

@Summer

Sure, we can figure this out.

We start by turning the operation signs around in your AND statement, making the true condition be a month that falls outside the current start/end dates. That leaves us with 0 when the date falls outside where we want it. Then if the date is where we want it, we nest another IF, comparing our Helper column to the [Systems to Be Completed Per Phase] column. If HelperColumn is greater than Phases, go with the Phases, otherwise, go with HelperColumn:

=IF(AND([2022 Start Date]@row < JANSTART#, [2022 Start Date]@row > FEBEND#, [2022 End Date]@row >= FEBEND#), 0, IF(HelperColumn@row > [Systems to Be Completed Per Phase]@row, [Systems to Be Completed Per Phase]@row, HelperColumn@row)) - [Jan Goal]@row

All comments

Summer

BUMP

Summer

Hi! Looks like the formula is returning the value of the NETWORKDAY([2022 Start Date]@row, FEBEND#) * [Phases Daily Run Rate (Constant)]@row function and when the month is not in range, the value will be negative.

I think I need to put in another IF statement to say if the result of the above calculation is < 0 than 0, but I can't pinpoint exactly where that IF statement should go.

Jeff Reisman

@Summer

When using NETWORKDAY (or NETWORKDAYS), if the first date value is later than the second date value, you get a negative number:

Swap the order, the result is positive:

The way your formula is written, with the dates you have in your data, the inner IF results in 0. 0 is not greater than value of Systems to Be Completed per Phase for the row, so your outer IF results in NETWORKDAY([2022 Start Date]@row, FEBEND#) * [Phases Daily Run Rate (Constant)]@row, which gives you a negative number.

I would take the NETWORKDAY([2022 Start Date]@row, FEBEND#) * [Phases Daily Run Rate (Constant)]@row calculation and move it to a helper column, since you use it twice in your formulas and need to add criteria of it being positive. Changing your outer false condition into an IF adds the criteria of HelperColumn@row (aka NETWORKDAY([2022 Start Date]@row, FEBEND#) * [Phases Daily Run Rate (Constant)]@row) being a positive number.

Summer

@Jeff Reisman

Thank you for attempting to tackle this with me! I was beginning to lose hope. Also, thank you for your insights on the order of the NETWORKDAYS and Date Value.

I created a helper column to mimic what you did in the example. On my actual sheet, I'll need a helper cell for each of the 12 months since the NETWORKDAYS * Phases will change based on the month I'm in.

I get an "#INCORRECT ARGUMENT SET" error when using this formula:

=IFERROR(IFERROR(IF(IF(AND([2022 Start Date]@row > JANSTART#, [2022 Start Date]@row < FEBEND#, [2022 End Date]@row <= FEBEND#), HelperColumn@row, 0) > [Systems to Be Completed Per Phase]@row, [Systems to Be Completed Per Phase]@row, IF(HelperColumn@row > 0, HelperColumn@row, 0), "") - [Jan Goal]@row, 0)

I feel like the base formula is so much more complicated than it should be. Overall what I'm looking for is to have the total number of work orders broken out into number of work orders to be completed per month during the contract start and end dates. If I have 100 work orders and my contract is three months (Say May, June, July), I only want to see a work order break down for those three months, totaling 100 work orders.

Current Logic

Is the Current month within the Start and End Dates? If FALSE than 0. Can I stop the formula there and just show zero if the result is "False"?

IF TRUE than calculate the value of NETWORKDAY([2022 Start Date]@row, Current Month) * [Phases Daily Run Rate (Constant)]@row and compare it to [Systems to Be Completed Per Phase]@row.

If NETWORKDAY*Phases is greater than [Systems to Be Completed Per Phase] than show me the value of [Systems to Be Completed Per Phase]

If NETWORKDAY*Phases is Less than [Systems to Be Completed Per Phase] than show me the value of NETWORKDAY*Phases

Then subtract all previous months values from the calculation.

Jeff Reisman

@Summer

Sure, we can figure this out.

Summer

@Jeff Reisman

I think you did it! Thank you!!

I updated the helper column for each month to calculate Phases * NETWORKDAY and added an IF statement so if it's less than 0 return 0.

=IF($[Phases Daily Run Rate (Constant)]14 * NETWORKDAY($[2022 Start Date]14, JANEND#) < 0, 0, $[Phases Daily Run Rate (Constant)]14 * NETWORKDAY($[2022 Start Date]14, JANEND#))

Then I updated the references in the simplified formula you previously provided, and manipulated the start and end dates to gauge the monthly results.

I'm seeing 0 in the months that are not relevant to my start and end dates, and the numerical breakdown for the correct months is accurate.

I appreciate all the time and effort you put into helping me. Thank you so much.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/319610#Comment_319610

Glad I could help! I try my best to show why something works the way it does, and it's a great feeling when the person molds it to fit for them, showing they truly learned something from my assistance. 🙂

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.