Percentage calculation

lesmickin

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

Ray Lindstrom

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

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

Ray Lindstrom

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!