# Would like to calculate delay reason

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.

Thanks

• 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:

Cheers!

Genevieve

• 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?

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

• 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:

Cheers!

Genevieve

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

• 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))

• 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

• 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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!