Search code examples
excelfunctionindexingexcel-formulamax

Find how many people the person with the most units per alcohol per week lives with

I have the following data collected:

enter image description here

hhsize: household size

drating: totals units of alcohol per week

I am trying to find a function that tells me how many people live in the house where the individual who drinks the most units of alcohol per week lives.

I know how to find the individual that drinks the most:

=MAX(data!Q11:Q9291)

But not how to link the result to the hhsize. EDIT: The answer must be formula or function based, i.e not generated solely by sorting or filtering.

Solution

  • You could edit the below to match your needs:

    enter image description here

    Formula in D1:

    =@SORT(A2:B9,2,-1)

    Note that the 2nd parameter holds the index of the column you wish to sort by. In my case '2', in yours it's probably '14'.