Returning the highest value with different row lengths in Excel

Question

I am working on an Excel file that gives different shareholder ratios per company. Now, I want to find the highest value of those ratios per company. The issue is that the number of ratios is different per company. So, company X has 11 shareholders and company Y has 7 shareholders. These ratios are presented in different rows, while the company name is in the first row (different column though).

Now, I want to return the highest ratio per company.

This is how the excel file looks:

Company X   Company X   51,42
            Company X   n.a.
            Company X   n.a.
            Company X   3,58
            Company X   2,96
            Company X   2,33
            Company X   1,18
            Company X   n.a.
            Company X   0,79
            Company X   0,71
            Company X   0,66
Company YON Company YON n.a.
            Company YON n.a.
            Company YON 8,94
            Company YON n.a.
            Company YON n.a.
            Company YON 1,03
            Company YON 0,91

Thanks for your question! So, for Company X the output would be 51,42. For Company Y this would be 8,94. — Geert, Mar 25 '18 at 15:21

QHarr · Accepted Answer · 2018-03-25T15:34:46.600

Create a pivottable and add the ratio to the values area and have Max of value. I have assume "," in numbers is your locale for "." in UK.

Summarizing values by max

Or use an array formula with a list of your companies (for example in I8 downwards) and put the following in J8 and drag down as far as required. You could restrict B:B and C:C to just rows with data in. Enter with Ctrl + Shift + Enter as array formulas

=MAX(IF(B:B=I8,C:C))

Result with look like:

{=MAX(IF(B:B=I8,C:C))}

Returning the highest value with different row lengths in Excel

1 Answers1