+ Reply to Thread
Results 1 to 5 of 5

Average calculations coming out slightly too high

  1. #1
    Registered User
    Join Date
    10-12-2018
    Location
    UK
    MS-Off Ver
    2007
    Posts
    2

    Average calculations coming out slightly too high

    Hi,

    Am running a sheet for my local darts competition. Got an issue when calculating a players average score. A darts average is the traditional way of measuring a players performance. It is points scored divided by darts thrown, multiplied by 3 (as there's 3 darts per throw).

    So in my dummy sheet the player has in total scored 24801 in 1057 darts so his average should be 70.390. But the average excel is giving me when either highlighting or in a pivot table is 71.748. I can't see where the difference is coming from as all the individual legs (rows on the sheet) appear to be giving the correct average for each leg.

    I get this on every player I am doing. Everyone is showing a slightly higher average than they should be. Can anyone see the reason for the difference? I'm sure there's a simple reason but I'm stuck as to what it is.
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,640

    Re: Average calculations coming out slightly too high

    Welcome to the forum!

    This is to do with weightings, I think. If the darts used were the same every time, then the two calculations would match, but they are not. By doing the calculations individually and then averaging these out, the weightings are correct (i.e. each match carries the same weighting regardless of the number of darts used). I would say that the higher score is the correct one.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    10-12-2018
    Location
    UK
    MS-Off Ver
    2007
    Posts
    2

    Re: Average calculations coming out slightly too high

    Ah yes of course! I'm averaging the per leg average not the combined totals. Doh! Thankyou.

    Total scored divided by total thrown is the usual way a darts average is shown. How would I get that number to appear when I pivot table the stats?

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,640

    Re: Average calculations coming out slightly too high

    Don't know - I never use pivot tables, sorry!

  5. #5
    Forum Expert dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Re: Average calculations coming out slightly too high

    Good afternoon dave180
    Quote Originally Posted by dave180 View Post
    How would I get that number to appear when I pivot table the stats?
    From the "Analyze" tab, under the "Actions" group, look at Fields, Items & Sets > Calculated Field.

    This will allow you to create your own custom formula for insertion into the pivot table.

    HTH

    DominicB
    Please familiarise yourself with the rules before posting. You can find them here.

+ 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. [SOLVED] High-low-average chart with 3 lines per process
    By dreddster in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 06-09-2015, 03:31 AM
  2. [SOLVED] Entered inside zone and coming outside values calculations
    By thilag in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-15-2014, 04:52 AM
  3. HELP!!! Using Multiple IF Statements...then coming up with an average.
    By jgsuf in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-22-2014, 04:39 PM
  4. Replies: 6
    Last Post: 01-25-2013, 01:33 PM
  5. Milestones with high/low/average
    By Jennigma in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 12-02-2012, 05:34 PM
  6. Replies: 1
    Last Post: 07-19-2010, 01:12 PM
  7. Values not coming in few cell but coming in rest all cells
    By rashmib in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-02-2009, 11:19 AM

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