Grand average of column averages per category

1. 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.

Best,
Phill

2. Re: Grand average of column averages per category

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.

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