+ Reply to Thread
Results 1 to 2 of 2

Grand average of column averages per category

  1. #1
    Registered User
    Join Date
    03-08-2019
    Location
    Denmark
    MS-Off Ver
    2013
    Posts
    51

    Grand average of column averages per category

    Hi there,

    I am looking for a simpler formula to the one I am currently using (although I do get the correct result now).

    My data set consists of a "bidding round", where each supplier (denoted by their "company_ID") can contribute with an offer. See below:

    Company_ID Bidding_round % offered
    2 339 0,1601029
    3 339 0,25131899
    4 339 0,425201123
    2 340 0,497990223
    2 354 0,3796
    4 354 0,356
    3 354 0,2728
    1 354 0,52
    2 341 0,444
    19 339 0,403124415
    20 354 0,583024

    The end goal is to calculate a grand total average of the average of each category separately. That is, not by adding up all the "% offered" and divide by the number of offers (8), but rather by finding the average for company 2, 3, and 4, respectively, and then averaging those three numbers to obtain the grand total average. One extra condition is that a supplier must have submitted at least two offers to be included in the average (which is why supplier 1, 19 and 20 is excluded in the pivot). Currently, the calculated column "Bud[at least two offers from same supplier]" returns true if this is true, and nothing if false, and that column is then used as a report filter.

    So far, I have been using the following code:

    Please Login or Register  to view this content.
    Since I am operating with a supplier list of more than 20 suppliers, this formula is not really scalable, so I am looking for a better alternative.

    I have included two images below of output of the Pivot Table for visualization. The column "correct average" contains the correct values, specifically 34,1% (I only need the total, that is, the 34,1%)

    Sample image 1.png

    Sample image 2.png

    339, 340, 341, and 354 are the bidding rounds.

    Enclosed, please find a sample workbook.

    Thank you in advance.

    Best,
    Phill
    Attached Files Attached Files
    Last edited by Phil123456789; 03-06-2020 at 06:07 AM.

  2. #2
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,125

    Re: Grand average of column averages per category

    Administrative note

    Welcome to the forum

    in your haste to solve your problem, you probably missed the yellow banner advising how to get answers faster by posting a sheet ?

    Please take a moment to read it and attach a sheet accordingly.

    Thanks you for helping us help you
    Be very, very careful using IFERROR ! It hides ALL errors which is not always what you want to get correct results

+ 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. Replies: 11
    Last Post: 09-27-2019, 08:00 AM
  2. Replies: 1
    Last Post: 09-11-2019, 03:07 AM
  3. [SOLVED] Is it possible to have both Grand Total and Grand Average in a Pivot Table?
    By ANS in forum Excel Charting & Pivots
    Replies: 15
    Last Post: 10-16-2012, 05:15 AM

Tags for this Thread

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