I have a spreadsheet containing data from hospital patients. I need to create a function (or a macro) that tells me how many people live in the household that has the biggest age difference between the oldest and the youngest person. This is how my data looks like : EDIT: I've changed the screenshot of the data for a table so it's easier to work with.

where : age: age of the patient hserial: serial number of household. This is how we identify a household. hhsize: household size

I was thinking on maybe using the filter function, and finding the maximum between the subtraction of the oldest and youngest of each household.

CodePudding user response：

You can try the following in E2 cell:

=LET(hs, A2:A10, hsize, B2:B10, age, C2:C10, ux, UNIQUE(hs),
 diff, MAP(ux, LAMBDA(u, LET(f, FILTER(age, hs=u), MAX(f)-MIN(f)))),
 x, XLOOKUP(MAX(diff), diff, ux), INDEX(hsize, XMATCH(x, hs)))

You can use instead of INDEX/MATCH the following XLOOKUP(x, hs, hsize).

Here is the output:

Please verify it.