Formula assistance

Char Norman

I am trying to create a formula for one column titled "Stats", and it is based of 4 other columns. What I need Stats to reflect is:

0 (zero) if column titled "Facilitator Name" is populated with a (applicant) name

1 if column titled "Application Status " is populated with "Accepted"

2 if column titled "Facilitator Status - Certificate Completed" is populated with "Approved"

3 if column titled "Pause New Grp Reg Reminder" is populated with a check mark

I have tried several different IFS options but cannot find a solution.

Thanks for any help!

Ray Lindstrom

Char,

Looking at the formula I provided and the columns names you have, I now see I didn't create the same exact named columns as you. Here is the formula updated to better match your column names. I used "Facilitator Status" when it should have been "Facilitator Status - Certificate Completed". That would have made it UNPARSEABLE. I've retested, and hopefully this edited one will do the trick.

=IF([Pause New Grp Reg Reminder]@row = 1, 3, IF([Facilitator Status - Certificate Completed]@row = "Approved", 2, IF([Application Status]@row = "Accepted", 1, 0)))

I personally wouldn't separate the columns.

Ray Lindstrom

Hi @Char Norman,

I would use nested IF statements, and I would do them in reverse order of what you have listed. Reason being is that once a statement is true the IF cycle is broken out of and the corresponding action is taken.

If you write it in the order you have it, every row with a Facilitator Name populated will get a 0, even any of the other items are true.

I believe this will do it:

=IF([Pause New Grp Reg Reminder]@row = 1, 3, IF([Facilitator Status]@row = "Approved", 2, IF([Application Status]@row = "Accepted", 1, 0)))

Here's from my testing:

Hope that helps!

All the best,

-Ray

Char Norman

Hi Ray,

I copied your formula into my Result cell but received an #UNPARSEABLE error.

Looking at the formula I tried to break it down to see if I could determine where the error was originating. I used the Pause New Grp Reg Reminder section but still received the same error. When I altered the formula, my logical expression worked when I entered this piece of the formula =IF([Pause New Grp Reg Reminder]:[Pause New Grp Reg Reminder], but it errored again when I added any values.

I wonder if I should just create 3 separate Results columns for each column.

Thanks for your help!

Char Norman

Ray,

By separating the column results I wonder if I can then pull the data into one report that will total the amount in the columns for me.

Thoughts?

Ray Lindstrom

Char,

Looking at the formula I provided and the columns names you have, I now see I didn't create the same exact named columns as you. Here is the formula updated to better match your column names. I used "Facilitator Status" when it should have been "Facilitator Status - Certificate Completed". That would have made it UNPARSEABLE. I've retested, and hopefully this edited one will do the trick.

=IF([Pause New Grp Reg Reminder]@row = 1, 3, IF([Facilitator Status - Certificate Completed]@row = "Approved", 2, IF([Application Status]@row = "Accepted", 1, 0)))

I personally wouldn't separate the columns.

Char Norman

Hi Ray,

THAT WORKED GREAT! Thank you. I didn't notice the name either, so thanks again for that great eye.

Certainly appreciate your help!

Have a great day,

Char

Ray Lindstrom

Great news, so glad it worked!

Thanks for the update. Hope you have a great day too!

BRgds,

-Ray