excelfilterexcel-formulamaxifsexcel-2021

Find value with the biggest age difference

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.

Solution

  • 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: excel output