Find value with the biggest age difference

Question

I have a spreadsheet containing data from hospital patients. I'm running Excel 2021. 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.

hserial	hhsize	age
101051	1	92
101151	1	63
101201	1	56
101271	2	38
101271	2	25
101351	3	37
101351	3	14
101351	3	10
101371	2	35
101371	2	29

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.

Answer 1

You can try the following in E2 cell for O365:

=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).

For Excel 2021 you don't have MAP available, but you can use the following approach that replaces the second line of the previous formula and uses XLOOKUP instead of INDEX/XMATCH, but you can use them too:

=LET(hs, A2:A10, hsize, B2:B10, age, C2:C10, ux, UNIQUE(hs),
 diff, MAXIFS(age,hs, ux) - MINIFS(age,hs, ux),
 x, XLOOKUP(MAX(diff), diff, ux), XLOOKUP(x, hs, hsize))

Here is the output for the first formula, for the second you get the same result:

Answer 2

Excel 2021 does not have LAMBDA functions, but you still can do so using the following:

=LET(rng,  A2:C11,
     hs,   INDEX(rng,,1),
     hh,   INDEX(rng,,2),
     age,  INDEX(rng,,3),
     mx,   MAXIFS(age,hs,hs),
     mn,   MINIFS(age,hs,hs),
     diff, mx-mn,
INDEX(hh,XMATCH(MAX(diff),diff)))

Or if it could be multiple different hhsize values with the same difference in age:

=LET(rng,  A2:C11,
     hs,   INDEX(rng,,1),
     hh,   INDEX(rng,,2),
     age,  INDEX(rng,,3),
     mx,   MAXIFS(age,hs,hs),
     mn,   MINIFS(age,hs,hs),
     diff, mx-mn,
UNIQUE(FILTER(hh,diff=MAX(diff))))

It takes the full range A2:C11 and divides it into separate named ranges: hs for hserial, hh for hhsize and age. Than it calculates the conditional max value mx of the age where the hs value in the range equals itself. Same for mn but this is the min value. Than diff is an array of the difference between mx and mn.

Than either INDEX / MATCH or FILTER is used to get the hh value in the row of the max value in diff