Would like to calculate delay reason

Joseph Aloysias - vSaaS Global · 11/29/22

Hi ,

I can get delay days from the "variance" column, but I want to find out maximum delay from this and paste it in the first row of "delay Reason"

say for example in screenshot its Raw material, I want to put the "Raw material" the first row.

One condition here, "scope changed" shouldn't consider as delay.

Please help me to achieve this.

Thanks

Genevieve P. · 12/05/22

Hi @Domnic Victor

Try something like this:

=INDEX([Delay Reason]:[Delay Reason], MATCH(MIN(COLLECT(Variance:Variance, [Delay Reason]:[Delay Reason], <>"Scope Changed")), Variance:Variance, 0))

You can only use this once in the Delay Reason column because you're referencing the column inside the formula.

It's looking through the Delay Reason and finding the matching row that has the MINIMUM value in Variance, excluding any rows with "Scope Changed".

It's a step more complicated than the usual Index(Match formula, but here's a Help Article that goes through the generic structure:

Formula combinations for cross sheet references | Smartsheet Learning Center

https://help.smartsheet.com/articles/2482647-cross-sheet-formula-combinations

There are countless ways to combine functions to analyze your data and make it more useful.

Cheers!

Genevieve

Captain Cowtown · 12/01/22

Hi,

This looks like you will need a combo formula of VLOOKUP and MIN. The MIN formula will identify the lowest number and then VLOOKUP can grab the adjacent cell.

Does that help?

Joseph Aloysias - vSaaS Global · 12/02/22

Can you please write the formula? I'm new to formulas

Genevieve P. · 12/05/22

Hi @Domnic Victor

Try something like this:

=INDEX([Delay Reason]:[Delay Reason], MATCH(MIN(COLLECT(Variance:Variance, [Delay Reason]:[Delay Reason], <>"Scope Changed")), Variance:Variance, 0))

You can only use this once in the Delay Reason column because you're referencing the column inside the formula.

It's looking through the Delay Reason and finding the matching row that has the MINIMUM value in Variance, excluding any rows with "Scope Changed".

It's a step more complicated than the usual Index(Match formula, but here's a Help Article that goes through the generic structure:

Formula combinations for cross sheet references | Smartsheet Learning Center

https://help.smartsheet.com/articles/2482647-cross-sheet-formula-combinations

There are countless ways to combine functions to analyze your data and make it more useful.

Cheers!

Genevieve

Joseph Aloysias - vSaaS Global · 12/05/22

Hi @Genevieve P. Awesome, its working! Thank you so much

Joseph Aloysias - vSaaS Global · 03/07/23

Hi @Genevieve P. I have a problem in calculating the delay reason, even if there is no delay in the project its getting Raw material as delay, I would like add "On time" if there is no delay ("0"), Could you please help me with the formula?

here is the existing formula,

=INDEX([Delay Reason]:[Delay Reason], MATCH(MAX(COLLECT([Delay in Days]:[Delay in Days], [Delay Reason]:[Delay Reason], <>"Scope Changed")), [Delay in Days]:[Delay in Days], 0))

Genevieve P. · 03/07/23

Hi @Domnic Victor

Can you clarify what you're bringing back with the INDEX formula? This formula looks to be bringing back the words (e.g. "Raw Material") if that row has the highest number of days delayed. Is that correct? Is the formula placed in that top row, where it says "Raw Material" currently?

You can add an IF statement at the beginning to say that IF the MAX number = 0, return "On time".

Try:

=IF(MAX(COLLECT([Delay in Days]:[Delay in Days], [Delay Reason]:[Delay Reason], <>"Scope Changed")) = 0, "On Time", INDEX([Delay Reason]:[Delay Reason], MATCH(MAX(COLLECT([Delay in Days]:[Delay in Days], [Delay Reason]:[Delay Reason], <>"Scope Changed")), [Delay in Days]:[Delay in Days], 0)))

Cheers,

Genevieve

Joseph Aloysias - vSaaS Global · 03/07/23

Yes, you are right, the maximum delay days would match the reason and put it in the top of the row.

This formula works great. Thank you so much

Formulas and Functions

Formulas and Functions

Would like to calculate delay reason

Best Answer

Formula combinations for cross sheet references | Smartsheet Learning Center

Answers

Formula combinations for cross sheet references | Smartsheet Learning Center

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

Formula - use % complete to determine whether in progress

Formula error

TRYING TO FIND THE ERROR IN THIS FORMULA