I have the following Excel spreadsheet:
A B C D
1 Sales Price Sales Price Sales Price
2 January February March
3 Year 01 50 70 90
4 Year 02 50 60 40
5 Year 03 60 70 30
6 Year 04 40 60 50
7 Year 05 50 40 25
8
9 Majority Sales Price: 50 70 90
In Rows 3-7 you see the sales price of a product from the months January-March
in 5 years (Year 01 - Year 05
).
In Row 9 I want to select the sales price which has the majority within each month.
For example in January
the sales price of 50
has the majority because it appears both in Year 01
and Year 02
.
Please keep in mind the following criterias:
a) If there is no majority of a sales price the highest sales price should be picked. (Cell D9)
b) If two or more sales prices have the same majority the higher one should be picked. (Cell C9)
What formula do I need to get my desired result?