+ Reply to Thread
Results 1 to 2 of 2

Calculating an average (Autosum does not work for this scenario)

  1. #1
    Registered User
    Join Date
    10-13-2013
    Location
    California
    MS-Off Ver
    2007
    Posts
    12

    Calculating an average (Autosum does not work for this scenario)

    As background, I'm a medical transcriptionist and in the spreadsheet I am creating, I want to calculate the average accuracy score and the average turnaround time (from when a report was dictated to when it was transcribed - called TAT). The accuracy score is reflected by subtracting error values from 100 to get your score. The TAT score is calculated by subtracting the dictation date and time from the transcription date and time. The worksheet is set up with 40 sets of 3 columns. Above each the three columns are some headings to identify the Job ID, who dictated the report, who transcribed it and the date and time it was dictated and the date and time it was transcribed. Under those two lines, is the caclulated TAT reflected in hours and minutes. Below these headings are the three columns I spoke of earlier. They are labeled - # Of Errors, Error Value, Total Value.

    D Date/Time: 10/12/13 12:30 AM D Date/Time: D Date/Time:
    T Date/Time: 10/12/13 5:48 AM T Date/Time: T Date/Time
    TAT: 5:18 TAT: 0.00 TAT: 0.00


    # of Errors Error Value Total Value # of Errors Error Value Total Value #of Errors Error Value Total Value
    1 3 3 0 3 0 0 3 0
    2 3 6 0 3 0 0 3 0

    Totals: 9
    Total QA Score: 91.00 100.00 100.00
    Average QA score: 97.00
    Average TAT:

    So there are 40 of these three column sets, with many categories down the left side of the spread sheet that make up the errors found in a report and then the number of errors are inserted and the Total Value is calculated.

    Now here's where I need help or an explanation of what's happening. When I input the data in the first grouping and the TAT is calculated at 5:18, shouldn't the Average TAT score also be 5:18, because the other values in the worksheet for that category are 0? But that's not what is reflected on the worksheet when I use Autosum Average for the three TAT cells, it's 1:46.

    The Autosum Average is working correctly for the Average QA score if all 40 sets are filled in, but if they are not, the empty columns with a score of 100 are falsely altering the score. So I need a formula that will start reflect a 0.00 in the cell but when subtracting the total errors, it will be subtracted from 100. and any empty totals will not alter the outcome. But then I am afraid I'll be in the same boat with the Average TAT score not reflecting accurately even when the other cells are at 0.00. Does all this make sense to anyone out there. Any help would be greatly appreciated.

  2. #2
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Calculating an average (Autosum does not work for this scenario)

    You could use the AVERAGEIF function to test whether or not there is a Total Error value present for each transcription. Then it would only average those where it is true and disredarg the extra 100.0's...

    - Moo

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. AutoSum shotcut doesn't work
    By bristly in forum Excel General
    Replies: 20
    Last Post: 01-22-2017, 11:44 AM
  2. Interesting Average Scenario
    By elfishio in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-10-2011, 01:15 PM
  3. Can't get autosum to work
    By grahalex in forum Excel General
    Replies: 2
    Last Post: 01-18-2011, 02:37 PM
  4. Excel 2007 : Autosum doesn't work
    By LearnerEXL in forum Excel General
    Replies: 2
    Last Post: 09-24-2009, 05:55 AM
  5. Autosum/Average/Count
    By Eagle68 in forum Excel General
    Replies: 4
    Last Post: 01-06-2006, 05:30 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1