Formula for numbering rows based on specific conditions

Brooks

Hello!

I need to create formulas for two columns that number rows based on specific conditions. I have a helper column (Number).

First, I want to number rows that: Type is "Focus Group Volunteer" and Internal is "yes."

Second, I want to number rows that: Type is "Focus Group Volunteer", Internal is "no", and Patron Type is "Faculty", "Instructor", or "Visiting Scholar."

When I added my formulas, the second is skipping 3 for some reason. Any assistance is greatly appreciated!

Here is an example:

Formula for "Number: Internal" column:

=IF(Type@row = "Focus Group Volunteer", IFERROR(RANKEQ(Number@row, COLLECT(Number:Number, [Internal]:[Internal], OR(@cell = "Yes")), 1), ""))

Formula for "Number: External Faculty" column:

=IF([Internal]@row = "No", IF(Type@row = "Focus Group Volunteer", IFERROR(RANKEQ(Number@row, COLLECT(Number:Number, [Patron Type]:[Patron Type], OR(@cell = "Faculty", @cell = "Instructor", @cell = "Visiting Scholar")), 1), "")))

DKazatsky2

Hi @Brooks,

This is happening because the COLLECT statement is including the row with "Feedback" as a type, we need to ensure the COLLECT only has the "Focus Group Volunteer".

Try this one.

=IF(AND(Internal@row = "No", Type@row = "Focus Group Volunteer"), IFERROR(RANKEQ(Number@row, COLLECT(Number:Number, Type:Type, "Focus Group Volunteer", Internal:Internal, "No", [Patron Type]:[Patron Type], OR(@cell = "Faculty", @cell = "Instructor", @cell = "Visiting Scholor")), 1), ""))

Dave

DKazatsky2

Hi @Brooks,

Even though your initial IF is excluding the 2nd row, the COLLECT is including it in the results, you need to tweak the COLLECT portion. Try this.

=IF(AND(Internal@row = "No", Type@row = "Focus Group Volunteer"), IFERROR(RANKEQ(Number@row, COLLECT(Number:Number, Internal:Internal, "No", [Patron Type]:[Patron Type], OR(@cell = "Faculty", @cell = "Instructor", @cell = "Visiting Scholor")), 1), ""))

Hope this helps,

Dave

Brooks

Hi @DKazatsky2 ,

Thank you for your quick reply! I made the change that you suggested and we got 3 back. But sadly, it's skipping 4 now.

DKazatsky2

Hi @Brooks,

This is happening because the COLLECT statement is including the row with "Feedback" as a type, we need to ensure the COLLECT only has the "Focus Group Volunteer".

Try this one.

=IF(AND(Internal@row = "No", Type@row = "Focus Group Volunteer"), IFERROR(RANKEQ(Number@row, COLLECT(Number:Number, Type:Type, "Focus Group Volunteer", Internal:Internal, "No", [Patron Type]:[Patron Type], OR(@cell = "Faculty", @cell = "Instructor", @cell = "Visiting Scholor")), 1), ""))

Dave

Brooks

That worked! Thank you for your help, Dave 😁