2

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?

Michi
  • 4,663
  • 6
  • 33
  • 83

1 Answers1

2

In cell B9 enter the following formula as an array, i.e. Ctrl+Shift+Enter:

{=IFERROR(MAX(MODE.MULT(B3:B7)), MAX(B3:B7))}

This uses MODE.MULT to find the modal value(s), which returns the result as an array. If there is more than one modal value, MAX returns the highest out of that array. If there is no modal value, MODE.MULT throws an error, hence the IFERROR statement which is used to find the max value in the error case.

mike7mike
  • 433
  • 1
  • 5
  • 16
  • 1
    Great solution. I was working on something with `SUMPRODUCT` but this is much more elegant. Never knew of the `MODE.MULT` function until now. – ImaginaryHuman072889 Nov 01 '17 at 14:45
  • Thanks it works perfectly. I actually do not need the Ctrl+Shift+Enter. It seems to work without the array as well or is there something I should consider? – Michi Nov 01 '17 at 14:46
  • 1
    @Michi, MODE.MULT returns an array of values so should require the formula to be entered as an array, i.e. with the {}. However I have just tried it without the {} and still got the same result... In short, I don't know. I'd include it as an array formula to avoid any unexpected results. – mike7mike Nov 01 '17 at 14:56
  • Just figured out the difference. The array formula is only necessary if I want to display ALL values in case there are values with the same majority. In my example above this would apply to Column C because the 60 and the 70 have the same majority. I would have to insert the array formula in Cell C9 and Cell C10 to display both values. However, due to the critiera b) in my question I do not want to display both values. I only want to show the highest one. Therefore, the MODE.MULT function without array combined with the MAX function gives back the correct result. – Michi Nov 02 '17 at 08:22