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

Christina09

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!

Mike TV

@Christina.R

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)

James Keuning

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?

Christina09

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

From admin to coordinator, there are Adam, Chelsea, Tom, and John.

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.

Mike TV

@Christina.R

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.

Mike TV

@Christina.R

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)

Mike TV

@Christina.R

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

Coordinator to Admin % formula:

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

Admin to Coordinator % formula:

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

Christina09

@Mike TV

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

Mike TV

@Christina.R

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)

Christina09

@Mike TV

OMG! This works like a charm!

Thanks for figuring this out :)