Would like to calculate delay reason

Joseph Aloysias

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.

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:

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

Cheers!

Genevieve

Captain Cowtown

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

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

Genevieve P.

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:

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

Cheers!

Genevieve

Joseph Aloysias

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

Joseph Aloysias

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.

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

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