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

Christina09
Christina09 ✭✭✭✭✭✭
edited 05/19/22 in Formulas and Functions

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:

Best Answer

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Answer ✓

    @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)

Answers

  • James Keuning
    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
    Christina09 ✭✭✭✭✭✭
    edited 05/19/22

    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
    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
    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
    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
    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
    Mike TV ✭✭✭✭✭✭
    Answer ✓

    @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
    Christina09 ✭✭✭✭✭✭

    @Mike TV

    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!