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, 2023 in Others by Kithuzzz
• 38,010 points
418 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, 2023 by narikkadan
• 63,420 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
• 63,420 points
401 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
• 63,420 points
3,072 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
• 63,420 points
597 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
• 63,420 points
452 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
• 63,420 points
581 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
• 23,260 points
1,347 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
• 63,420 points
647 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
• 63,420 points
2,431 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
• 63,420 points
6,649 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