Percentage calculation

Options

I have cases assigned to a group of investigators and for each investigator, i have a column indicating if a report was sent back for edits (check box).

What I am trying to do is calculate the percentage of reviews submitted that were error free.

So if investigator "leslie king" had a total of 47 cases and 2 of those were sent back for edits (45/47), then her success rate would 96%. But im struggling to write the correct formula for this.


Any help would be greatly appreciated

Answers

  • Ray Lindstrom
    Ray Lindstrom ✭✭✭✭✭✭
    edited 11/15/22
    Options

    Hi @lesmickin,

    Not sure if you have a column called cases, or if the number of cases is represented by the number of rows where "leslie king" appears. Here's a recommended solution for each depending on what you're trying.


    If Cases is a column, use this in the success rate column (formatted as percentage before pasting the formula):

    =SUM(1 - [Sent Back for Edits]@row / Cases@row)


    If the number of cases is represented by the number of rows where "leslie king" appears, try this:

    Create a Summary field for each investigator.

    Sent back for edits is a checkbox I would assume. Is that right?

    Paste this into each Investigator's summary field, and replace the investigator name for each.

    =SUM((100 - ((COUNTIF([Sent Back for Edits]:[Sent Back for Edits], 1)) / (COUNTIF(Investigator:Investigator, "leslie King"))) * 100))


    Hope this helps!

    BRgds,

    -Ray

  • JeannieRose
    Options

    Can anyone explain why this formula, when converted to percentage changes from 14.9 (the correct amount) to 1,492% ??

  • Ray Lindstrom
    Ray Lindstrom ✭✭✭✭✭✭
    Options

    When converting a number to a percentage it pushes the decimal point over 2 places. A sample of why it does is because 1.0 equals 100%. I understand you don't want your numbers multiplied by 100, but that's the reason it's happening. Drives me crazy when I go to convert to percentage!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!