Max number pulled from the ID

Helena P.

Hi all,

I am looking for help with creating a formula which would give me the max number (based on condition) from combined ID?

Example of ID's

RTR_I_053

TPM_I_012

FTS_F_009

The condition would be if the ID contains example "_F_", give me the highest number for this item.

I tried =IF({Ref to ID column}, CONTAINS("_F_", @cell), MAX({Ref do ID column}))

but got the message Incorrect data type, I am guessing due to the ID which is combination of RTR (team), "_F_" letter represents type of item followed by the number.

KPH

Hi @Helena P.

The IF formula will not do what you want. The IF needs a logical expression that will be true or false for a particular cell, and then a value or formula to be returned if true, and optionally one if false. You can learn about IF here:

https://help.smartsheet.com/function/if

In your case, you are trying to evaluate a range to restrict the values that you calculate the MAX of. To do this you should include the criteria within your COLLECT. You only want to COLLECT rows where Ref column with type is "Form" (as well as those where Reference column with full ID CONTAINS "_F_"). Then you will find the MAX of the Reference column with just numbers in of the rows that are in the collection.

Like this:

=MAX(COLLECT({Reference column with just numbers in}, {Reference column with full ID}, CONTAINS("_F_", @cell),{Ref column with type}, "Form"))

KPH

Hi @Helena P.

I see a couple of problems.

1. You can't do MAX on a text string. Are the numbers always 3 digits at the end? If so you can use the RIGHT function to return just the last 3 digits. And wrap that in a VALUE function to convert it into a number. Then you can find the MAX of those. I would add a hidden column to the sheet, and use this as a column formula: =VALUE(RIGHT([Column name]@row, 3))
2. IF is looking for something to evaluate and the syntax is logic, value if true, value if false. It looks like you are putting a range instead of the logic, then an criterion, then something to return. I think a COLLECT function would suit you better. You can use COLLECT to find the rows with the matching ID and then use MAX on the result.

So, if your sheet has 2 columns, one with the Original ID in and one with just the value in, your formula would look like:

=MAX(COLLECT({Reference column with just numbers in}, {Reference column with full ID}, CONTAINS("_F_", @cell)))

Helena P.

thank you for replying, let me try this out and share the outcome :)

Helena P.

This worked thank you KPH :) I just realised I would need to add another condition to the search tough.

I tried with IF but I might have done in wrong order as I got unparseable error back.

I used your formula =MAX(COLLECT({Reference column with just numbers in}, {Reference column with full ID}, CONTAINS("_F_", @cell))) but added like below:

=IF({Ref column with type}, "Form"), MAX(COLLECT({Reference column with just numbers in}, {Reference column with full ID}, CONTAINS("_F_", @cell)))

KPH

Hi @Helena P.

The IF formula will not do what you want. The IF needs a logical expression that will be true or false for a particular cell, and then a value or formula to be returned if true, and optionally one if false. You can learn about IF here:

https://help.smartsheet.com/function/if

In your case, you are trying to evaluate a range to restrict the values that you calculate the MAX of. To do this you should include the criteria within your COLLECT. You only want to COLLECT rows where Ref column with type is "Form" (as well as those where Reference column with full ID CONTAINS "_F_"). Then you will find the MAX of the Reference column with just numbers in of the rows that are in the collection.

Like this:

=MAX(COLLECT({Reference column with just numbers in}, {Reference column with full ID}, CONTAINS("_F_", @cell),{Ref column with type}, "Form"))

Helena P.

HI KPH,

thank you so much for prompt reply :)

I used the above formula and it works! :)

KPH

Great! If you have any more criteria, just add them to the COLLECT function in the same way.

Helena P.

great, thank you again!