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

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:

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

• 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([email protected] = "Coordinator", [email protected] = "Start", INDEX(Role:Role, MATCH([email protected], Name:Name, 0)) = "Admin"), 1, 0)

AtC Formula:

=IF(AND([email protected] = "Admin", [email protected] = "Start", INDEX(Role:Role, MATCH([email protected], 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([email protected] = "Admin", [email protected] = "Start", INDEX(COLLECT(Name:Name, Role:Role, ="Coordinator", Name:Name, [email protected]), 1) = [email protected]), 1, 0)

AtC Formula:

=IF(AND([email protected] = "Coordinator", [email protected] = "Start", INDEX(COLLECT(Name:Name, Role:Role, ="Admin", Name:Name, [email protected]), 1) = [email protected]), 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([email protected] = "Admin", [email protected] = "Start", INDEX(COLLECT(Name:Name, Role:Role, ="Coordinator", Name:Name, [email protected]), 1) = [email protected], INDEX(COLLECT([Date record]:[Date record], Name:Name, [email protected], Role:Role, ="Coordinator"), 1) < [Date record]@row), 1, 0)

AtC Formula:

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

• OMG! This works like a charm!

Thanks for figuring this out :)