I have a dataset reflecting a process where suppliers ("company_ID") submits their offers ("% offered") in a tender round ("bidding_round").
- (1) Firms with 1-499 employees
- (2) Firms with 500+ employees
My end goal is to calculate the average price offered for either of the following two segments based on the user's input in another sheet ("Segment chosen?"), subject to two important criteria:
- (1) Only suppliers that have submitted at least 2 prices IN A SEGMET should be included (so supplier 19 and 20 should be excluded in 500+ segment, but supplier 20 should be included in the 1-499 segment where supplier 1 should then be exluded).
- (2) The average should be an average of each firm's average price (so ultimately e.g. 34,10% rather than 34,84% in the 500+ segment).