Breakdown formula not working correctly

Michaela Kamenska

Hi All,

So I've got this formula that automatically breaks hours down into respective columns based on dates:

=IFERROR(IF(AND(VALUE(YEAR($[MDR Start]@row) + "" + IF(MONTH($[MDR Start]@row) < 10, "0") + MONTH($[MDR Start]@row)) <= [Jan-21]$1, VALUE(YEAR($Finish@row) + "" + IF(MONTH($Finish@row) < 10, "0") + MONTH($Finish@row)) >= [Jan-21]$1), $[Total Budgeted]@row / COUNTIFS($[Jan-21]$1:$[Dec-28]$1, AND(@cell >= VALUE(YEAR($[MDR Start]@row) + "" + IF(MONTH($[MDR Start]@row) < 10, "0") + MONTH($[MDR Start]@row)), @cell <= VALUE(YEAR($Finish@row) + "" + IF(MONTH($Finish@row) < 10, "0") + MONTH($Finish@row))))), "")

It otherwise works absolute magic in my other sheets but it does not want to work in this sheet and I don't know why. It really should be showing values that are in the "Average Monthly Hours" but instead, it's showing values that are absolutely random. See below:

In my other sheets, even if I don't have columns for all the previous years, the formula is able to do the breakdown and populate cells correctly, but I can't figure why it doesn't want to work here. Can anybody please help?

I've tried even renaming all columns to match those where the formula works, only adjusted the [Mon-YY] cells as per this sheet and it still doesn't go.

Thanks in advance.

Paul Newcome

Ok. So here is a super simplified version of the formula:

=IF(AND(starting_yyyymm <= [Jan-21]$1, finish_yyyymm >= [Jan-21]$1), $[Total Budgeted]@row / COUNTIFS($[Jan-21]$1:$[Dec-28]$1, AND(@cell >= starting_yyyymm, @cell <= finish_yyyymm)))

It actually looks to me as if it is operating correctly. Basically we are taking the [Total Budget]@row and dividing it by the number of month columns that are overlapped by the start and finish dates.

So the first row of data has 25 month columns that fit within the start and end dates. 2098 divided by 25 is 83.92

The second row of data only has 1 month column that falls within the range (Jan-21). 441 divided by 1 is 441.

The third row of data only has 12 month columns that fall between the start and finish dates. 1591 divided by 12 is 132.583.

Basically, this particular solution assumes that there will be no start or finish dates that are not covered by the month columns. Another way of putting it is that there needs to be a month column to cover every month in between the start and finish dates.

Michaela Kamenska

Hi @Paul Newcome, I hope it's ok to tag you in this. This was a formula you originally created and it was adapted for needs of other sheet I had to create and the adapted formula (as above) worked just fine. So I'm hoping you'll have some insight into why it may not be working as intended to. Original post was here: https://community.smartsheet.com/discussion/69390/automatic-breakdown-of-hours-into-columns/p2

I really hope you can help me 😫🙏

Paul Newcome

Ok. So here is a super simplified version of the formula:

=IF(AND(starting_yyyymm <= [Jan-21]$1, finish_yyyymm >= [Jan-21]$1), $[Total Budgeted]@row / COUNTIFS($[Jan-21]$1:$[Dec-28]$1, AND(@cell >= starting_yyyymm, @cell <= finish_yyyymm)))

It actually looks to me as if it is operating correctly. Basically we are taking the [Total Budget]@row and dividing it by the number of month columns that are overlapped by the start and finish dates.

So the first row of data has 25 month columns that fit within the start and end dates. 2098 divided by 25 is 83.92

The second row of data only has 1 month column that falls within the range (Jan-21). 441 divided by 1 is 441.

The third row of data only has 12 month columns that fall between the start and finish dates. 1591 divided by 12 is 132.583.

Basically, this particular solution assumes that there will be no start or finish dates that are not covered by the month columns. Another way of putting it is that there needs to be a month column to cover every month in between the start and finish dates.

Michaela Kamenska

Hi @Paul Newcome ,

Thank you. This was very insightful. I totally didn't realise that duration was not taken into account.

In another sheet I've got the following formula as I needed to take 2 different sets of hours into account (courtesy of another really smart person on here), and only removed the second half and it works!!! Yay!

=IFERROR(IF(AND(([Oct-20]$1 >= YEAR($[Start-up Start Date]@row) + RIGHT("00" + MONTH($[Start-up Start Date]@row), 2)), ([Oct-20]$1 <= YEAR($[FPI/Start-Up End]@row) + RIGHT("00" + MONTH($[FPI/Start-Up End]@row), 2))), $[Start-Up Hours]@row / $[Start-Up Duration]@row, IF(AND(([Oct-20]$1 >= YEAR($[MDR Start]@row) + RIGHT("00" + MONTH($[MDR Start]@row), 2)), ([Oct-20]$1 <= YEAR($Finish@row) + RIGHT("00" + MONTH($Finish@row), 2))), $[Total Budgeted]@row / $[MDR Duration (months)]@row, "")), "")

Paul Newcome

I have been thinking on how to acomodate for rows where there is not a month column for each month between the start and finish dates.

Basically what we need to do is figure out how many months total are between the start and finish dates, and then divide the total budgeted by that.

((YEAR($Finish@row) - YEAR($[MDR Start]@row)) * 2) + (MONTH($Finish@row) - MONTH($[MDR Start]@row))

So we can keep the initial IF logic that determines which month column(s) the dollar amount belongs in but replace the COUNTIFS with the above so we have the correct duration calculated.

=IFERROR(IF(AND(VALUE(YEAR($[MDR Start]@row) + "" + IF(MONTH($[MDR Start]@row) < 10, "0") + MONTH($[MDR Start]@row)) <= [Jan-21]$1, VALUE(YEAR($Finish@row) + "" + IF(MONTH($Finish@row) < 10, "0") + MONTH($Finish@row)) >= [Jan-21]$1), $[Total Budgeted]@row / ((YEAR($Finish@row) - YEAR($[MDR Start]@row)) * 2) + (MONTH($Finish@row) - MONTH($[MDR Start]@row))), "")

Michaela Kamenska

I've adapted a formula from another sheet as below but what I found is that it somehow does not want to populate certain cells and gives me #INVALID OPERATION:

=IFERROR(IF(AND(([Jan-21]$1 >= YEAR($[Start-Up Date]@row) + RIGHT("00" + MONTH($[Start-Up Date]@row), 2)), ([Jan-21]$1 <= YEAR($[End Date (CSR)]@row) + RIGHT("00" + MONTH($[End Date (CSR)]@row), 2))), $[Total Budgeted incl MDR Oversight]@row / $[Duration (months)]@row), "")

I've found that e.g. in the US team sheet, whenever the Global Lead is EU, it just does not populate the cells and vice versa. I've got different formulas in each to reverse arguments and they are as follows:

Start-Up Date:

=IFERROR(IF([Global Lead]@row = "NA", INDEX({Ref #1}, MATCH([Study #]@row, {study #}, 0)), IF(AND([Global Lead]@row <> "NA", [Regional Start-Up]@row = ""), INDEX({Ref #1}, MATCH([Study #]@row, {study #}, 0)), INDEX({Ref #2}, MATCH([Study #]@row, {study #}, 0)))), "")

MM Hours (different from total budgeted):

=IFERROR(INDEX({Ref #3}, MATCH([Study #]@row, {study #}, 0)), "")

Budgeted Oversight Hours:

=IFERROR(INDEX(COLLECT({Ref #4}, {Global Lead}, @cell = "NA", {study #}, [Study #]@row), 1), "")

I'm wondering if these formulas in any way influence the main formula above? Because if I change "NA" in the formula to "EU" in the EU Team sheet, anywhere where NA is the global lead, the cells don't populate:

Paul Newcome

Check this: {Ref #1}

Double check that it is a date type column and that it is all date values (as opposed to a text value that just looks like a date) and also check that the error is not present in any single cell within that range.

Michaela Kamenska

Just checked and all values that are not blank are indeed a date. The other fields are also just "number/text" formatted columns

Paul Newcome

Ok. Lets check this next range and make sure they are all in fact numerical values:

{Ref #4}

How is the data in that range populated exactly?

Best way to check is to insert a temporary text/number column and use a basic IF column formula:

=IF(ISTEXT([Column Name]@row), "TEXT")

Then apply a temporary filter to the sheet to see if any rows contain "TEXT".

Michaela Kamenska

Ref #4 is only numbers or blanks. This is an optional task depending on client needs so the columns are not always populated. BTW I used similar formula to check the dates in my dates columns as =IF(ISDATE([column name], "Yes", "No") as well. The only ones that were not a date were blanks. Otherwise I checked settings on all columns and all are formatted as they should be.

Paul Newcome

What happens if you throw this into a temporary text/number column?

=$[Total Budgeted incl MDR Oversight]@row / $[Duration (months)]@row

Are you still getting the same error on the "NA" rows?

Michaela Kamenska

@Paul Newcome I've got that column in already, it's called the "Average Monthly Hours" and takes the oversight into account.

I'm not sure how to use that in the auto-breakdown/auto-populate formula.

Basically what I want is to have that formula copy-pasted on a whole row and if anything changes, e.g. the start up date changes, I want this to update automatically without manual edit in the rollup sheet.

Michaela Kamenska

@Paul Newcome I've been sitting on this for a few days now and decided to create the startup as identical to Lead Startup with an easy index match - now it matches easy without any issues... Now what I'm trying to say to my breakdown formula is that if the [Global lead] is EU, it should take the [Lead Start-Up Date], but if the Global Lead is <> EU and [Regional Start-Up date] is not blank, it should take the [Regional Start-Up Date] , else take the [Lead Start-Up Date]. I don't know how to incorporate this into the formula but could you help? I came up with this but it gives me #UNPARSEABLE (the rest of the "13.2" cells are a copy paste, populated manually):

To give more insight - A project may start in 12 Dec 2021 for NA but EU only joins in 01 January 2024. That's why it's important.

=IF(AND(([Global Lead]@row = "EU", [Feb-21]$1 >= YEAR($[Lead Start-Up Date]@row) + RIGHT("00" + MONTH($[Start-Up Date]@row), 2)), ([Feb-21]$1 <= YEAR($[End Date (CSR)]@row) + RIGHT("00" + MONTH($[End Date (CSR)]@row), 2))), $[Total Budgeted incl MDR Oversight]@row / $[Duration (months)]@row), IF(and(([global lead]@row <> "EU", NOT(ISBLANK([Regional Start-up Date]@row)), [Feb-21]$1 >= YEAR($[Regional Start-Up Date]@row) + RIGHT("00" + MONTH($[Start-Up Date]@row), 2)), ([Feb-21]$1 <= YEAR($[End Date (CSR)]@row) + RIGHT("00" + MONTH($[End Date (CSR)]@row), 2))), $[Total Budgeted incl MDR Oversight]@row / $[Duration (months)]@row),[Feb-21]$1 >= YEAR($[Lead Start-Up Date]@row) + RIGHT("00" + MONTH($[Start-Up Date]@row), 2)), ([Feb-21]$1 <= YEAR($[End Date (CSR)]@row) + RIGHT("00" + MONTH($[End Date (CSR)]@row), 2))), $[Total Budgeted incl MDR Oversight]@row / $[Duration (months)]@row))

Paul Newcome

My apologies for the delay. I must have missed the notification from your last response.

Lets start with the error. It looks like there may be some extra parenthesis tucked in and we are missing an IF/AND. We can try cleaning those up first and see how that works...

=IF(AND([Global Lead]@row = "EU", [Feb-21]$1 >= YEAR($[Lead Start-Up Date]@row) + RIGHT("00" + MONTH($[Start-Up Date]@row), 2), [Feb-21]$1 <= YEAR($[End Date (CSR)]@row) + RIGHT("00" + MONTH($[End Date (CSR)]@row), 2)), $[Total Budgeted incl MDR Oversight]@row / $[Duration (months)]@row, IF(and([global lead]@row <> "EU", NOT(ISBLANK([Regional Start-up Date]@row)), [Feb-21]$1 >= YEAR($[Regional Start-Up Date]@row) + RIGHT("00" + MONTH($[Start-Up Date]@row), 2), [Feb-21]$1 <= YEAR($[End Date (CSR)]@row) + RIGHT("00" + MONTH($[End Date (CSR)]@row), 2)), $[Total Budgeted incl MDR Oversight]@row / $[Duration (months)]@row, IF(AND([Feb-21]$1 >= YEAR($[Lead Start-Up Date]@row) + RIGHT("00" + MONTH($[Start-Up Date]@row), 2), [Feb-21]$1 <= YEAR($[End Date (CSR)]@row) + RIGHT("00" + MONTH($[End Date (CSR)]@row), 2)), $[Total Budgeted incl MDR Oversight]@row / $[Duration (months)]@row)))

Michaela Kamenska

Hi Paul,

No problem at all. Unfortunately, it is still giving me an error. What else do you think it not correct?

Paul Newcome

Is it the same error? Are you able to provide a screenshot of the formula in the sheet similar to the screenshot below?