EXCEL-Function to find how many people does the oldest female live with

0 votes

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.
Finding a formula that indicates how many persons the tallest female respondent lives with is my goal.
I am aware that I must use the IF and max functions, but I am missing the function that returns the persnum since I am unsure of which to use.
This is my formula so far (missing the persnum part)

=MAX(IF(data!G11:G9291=2,data!T11:T9291))
Jan 13 in Others by Kithuzzz
• 28,700 points
45 views

1 answer to this question.

0 votes

You can try something like this:

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

It doesn't matter which column you use as the first input parameter for FILTER; all that matters is that it have the same form as the second input argument. It presumes that column T in the representation of height. Similar to adding additional limitations to MAXIFS, you can add as many criteria as you like (the only restriction is that it needs to be a range, not an array). For instance, I'm not sure how you can tell if someone is living 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

answered Jan 13 by narikkadan
• 53,160 points

Related Questions In Others

0 votes
1 answer

how to show the <meta> values of yoast seo with some function?

check this folder : wordpress-seo\src\presenters\open-graph you can ...READ MORE

answered Feb 10, 2022 in Others by narikkadan
• 53,160 points
161 views
0 votes
1 answer

How to get the JasperReports into Excel format with proper alignment

Remove at least one parameter: exporter.setParameter(JRXlsExporterParameter.IS_COLLAPSE_ROW_SPAN,Boolean.TRUE) Note that JRXlsExportParameter is deprecated the correct ...READ MORE

answered Sep 21, 2022 in Others by narikkadan
• 53,160 points
642 views
0 votes
1 answer

How to use the INDIRECT function in Excel

Try this: =INDEX(INDIRECT($A$1&"!$1:$1048576"),MATCH($B$1,INDI ...READ MORE

answered Sep 29, 2022 in Others by narikkadan
• 53,160 points
150 views
0 votes
1 answer

How Can I Round Prices to the nearest 0.95 with an Excel Formula?

Try this: =IF(OR(A3-FLOOR(A3,1)>0.95,A3=CEILING(A3,1)),CEILING ...READ MORE

answered Oct 9, 2022 in Others by narikkadan
• 53,160 points
125 views
0 votes
1 answer

Help needed with Median If in Excel

Try entering the formula =MEDIAN(IF($A$1:$A$6="Airline",$B$1:$B$6,"")) in another ...READ MORE

answered Nov 10, 2022 in Others by narikkadan
• 53,160 points
65 views
0 votes
1 answer

Excel feet and inches to millimeters

There are certain direct formulas to convert ...READ MORE

answered Feb 23, 2022 in Database by gaurav
• 22,940 points
663 views
0 votes
1 answer

Convert image (jpg, png, jpeg) to base64

Try this - it will perform the ...READ MORE

answered Sep 24, 2022 in Others by narikkadan
• 53,160 points
217 views
0 votes
1 answer

IF function in combination with an Round function Excel

I'm not sure if it is a ...READ MORE

answered Sep 25, 2022 in Others by narikkadan
• 53,160 points
130 views
0 votes
1 answer

How to find out how many rows and columns to read from an Excel file with PHPExcel?

Solution: $file_name = htmlentities($_POST['file_name']); $sheet_name = htmlentities($_POST['sheet_name']); $number_of_columns = htmlentities($_POST['number_of_columns']); $number_of_rows ...READ MORE

answered Oct 23, 2022 in Others by narikkadan
• 53,160 points
1,466 views
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP