# Is there a way to calculate the change of role in percentage?

✭✭✭✭✭✭
edited 05/19/22

Hi,

I have a tracker, whenever the role/status change, it'll copy to this sheet. I'm trying to find a formula to calculate the change % from coordinator to admin, and from admin to the coordinator.

Is there a way to do so?

TIA!

Tags:

• ✭✭✭✭✭✭

Thanks for the exercise. It was a challenge but I think I figured it out!

CtA Formula:

=IF(AND(Role@row = "Admin", Status@row = "Start", INDEX(COLLECT(Name:Name, Role:Role, ="Coordinator", Name:Name, =Name@row), 1) = Name@row, INDEX(COLLECT([Date record]:[Date record], Name:Name, =Name@row, Role:Role, ="Coordinator"), 1) < [Date record]@row), 1, 0)

AtC Formula:

=IF(AND(Role@row = "Coordinator", Status@row = "Start", INDEX(COLLECT(Name:Name, Role:Role, ="Admin", Name:Name, =Name@row), 1) = Name@row, INDEX(COLLECT([Date record]:[Date record], Name:Name, =Name@row, Role:Role, ="Admin"), 1) < [Date record]@row), 1, 0)

• ✭✭✭✭✭

Can you mock up the result you’re looking for? Percentage of what? Do you mean the differences between those roles for the same name? How do the Date Record and Status columns impact this?

• ✭✭✭✭✭✭
edited 05/19/22

So from the role coordinator to admin, there are Jane, John, and Adam

Currently, I don't have a formula for the checkbox to identify who changed from coordinator to admin/ admin to coordinator, but with over 1000+ entries, I'll need a formula to help identify it.

The result should be 60% changed from coordinator to admin, and 80% changed from admin to coordinator.

The date record is to identify if they're a coordinator or admin before the role change. I'm also tracking the status change separately as well.

I hope there's a solution to this.

Thank you.

• ✭✭✭✭✭✭

I'm really close. Is anyone able to help fix up the formula to make it fully work? I'm still trying...

Example sheet:

Cta Formula:

=IF(AND(Role@row = "Coordinator", Status@row = "Start", INDEX(Role:Role, MATCH(Name@row, Name:Name, 0)) = "Admin"), 1, 0)

AtC Formula:

=IF(AND(Role@row = "Admin", Status@row = "Start", INDEX(Role:Role, MATCH(Name@row, Name:Name, 0)) = "Coordinator"), 1, 0)

The problem is CtA Formula isn't checking the box in row 11 for John and the AtC Formula isn't checking the box in row 12 for Adam. I've been working on an INDEX(COLLECT version but haven't quite gotten there yet.

• ✭✭✭✭✭✭

Ok, I got it working but you have to designate when someone's a new hire by calling their status something like "Fresh Start" or "New Hire". Then it works like so:

CtA Formula:

=IF(AND(Role@row = "Admin", Status@row = "Start", INDEX(COLLECT(Name:Name, Role:Role, ="Coordinator", Name:Name, =Name@row), 1) = Name@row), 1, 0)

AtC Formula:

=IF(AND(Role@row = "Coordinator", Status@row = "Start", INDEX(COLLECT(Name:Name, Role:Role, ="Admin", Name:Name, =Name@row), 1) = Name@row), 1, 0)

• ✭✭✭✭✭✭

For your overall goal of a % formula, you can use the following:

=COUNTIF([CtA Formula]:[CtA Formula], =1) / COUNTIF(Name:Name, <>"")

=COUNTIF([AtC Formula]:[AtC Formula], =1) / COUNTIF(Name:Name, <>"")

• ✭✭✭✭✭✭

I'm having trouble with the status. Is it possible to use a formula to identify the first entry instead of changing the status to "fresh start" and "start"?

TIA

• ✭✭✭✭✭✭

Thanks for the exercise. It was a challenge but I think I figured it out!

CtA Formula:

=IF(AND(Role@row = "Admin", Status@row = "Start", INDEX(COLLECT(Name:Name, Role:Role, ="Coordinator", Name:Name, =Name@row), 1) = Name@row, INDEX(COLLECT([Date record]:[Date record], Name:Name, =Name@row, Role:Role, ="Coordinator"), 1) < [Date record]@row), 1, 0)

AtC Formula:

=IF(AND(Role@row = "Coordinator", Status@row = "Start", INDEX(COLLECT(Name:Name, Role:Role, ="Admin", Name:Name, =Name@row), 1) = Name@row, INDEX(COLLECT([Date record]:[Date record], Name:Name, =Name@row, Role:Role, ="Admin"), 1) < [Date record]@row), 1, 0)

• ✭✭✭✭✭✭

OMG! This works like a charm!

Thanks for figuring this out :)

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!