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:
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
Bookmarks