0

I have the following data collected: spreadsheet

persnum: Person number within the household Age:age of the person, htval: height of the person

I have highlighted them in the screenshot.

I am looking for a formula that tells how many people does the tallest female in the survey live with.

I know that I need to use the max function, the IF function but I am missing the function that returns the persnum as I don't know which to use for this.

This is my formula so far (missing the persnum part)

=ROWS(@FILTER(data!D11:D9291,data!T11:T9291=MAXIFS(data!T11:T9291,data!G11:G9291,2)))-1

EDIT: It should be comptible with 2019 Excel version Thank you for your help

andrea65
  • 67
  • 8

1 Answers1

0

You can try something like this:

=ROWS(FILTER(B11:B9291, T11:T9291=MAXIFS(T11:T9291,G11:G9291,2)))

The column you use on first input argument of FILTER is irrelevant, it just needs to have the same shape as the second input argument. It assumes the hight is represented in column T. You can add as many conditions as you want to MAXIFS in a similar way, to include additional constraints (the only restriction is that it needs to be a range, not an array). For example I don't know how you identify the person is live based on the input data.

You can also use the entire column, if it makes sense for your case. As the following example:

excel output

David Leal
  • 6,373
  • 4
  • 29
  • 56
  • Thanks David. I've tried it and it displays a logical result. What do you mean with "identifying the person is live based on the input data"? – andrea65 Jan 12 '23 at 18:41
  • well, you are looking for a live person, but I don't if you have in your input data dead people, if so then you need to filter by this column too. – David Leal Jan 12 '23 at 18:43
  • 1
    Oh, no there are no dead people in this spreadsheet, but I see your point. Thanks for thinking on that too :) – andrea65 Jan 12 '23 at 18:44
  • Should I change something if I had the case that an individual shared the same value to display the first value that appears in the datablock? – andrea65 Jan 16 '23 at 17:27
  • 1
    @andrea65 but you asked for the total count, a quick fix is just to add `@`-operator, for example: `@FILTER(A:A, C:C=MAXIFS(C:C,B:B,2))` – David Leal Jan 16 '23 at 18:06
  • 1
    You mean @ with the filter right? See the updated code to see if that's what you mean. – andrea65 Jan 16 '23 at 18:07