Feb 18 2022 12:08 AM
Hi,
I have the attached workbook, and I have 2 problems:-
1) I need to create a lookup (hopefully X) which will return multiple columns, say F & J, if their age is greater than a value in actual age, say 40.
2) I would like to combine the formulas in columns G & H, so that it will work out from a date of birth someones age, but rounded down, like an age in the real world.
Many thanks in advance.
Feb 18 2022 12:23 AM - edited Feb 18 2022 12:23 AM
@Robert1290 With regard to point 2:
=ROUNDDOWN(((TODAY()-I5))/365.25,0)
You take the formula from H5, without the = sign, and place it in the ROUNDDOWN function in stead of H5.
Not sure what you want in point 1.
Feb 18 2022 12:33 AM - edited Feb 18 2022 12:34 AM
@ Riny_van_Eekelen thank you, that is so obvious now you have said it, and will help me in the future with other formulas.
For point 1, say if the actual age is more than 40, then I would like to return, say, their name and reference on to a new worksheet.
Feb 18 2022 12:37 AM
1. I recommend using filter instead of xlookup since you have office 365.
The formula should be this for returning the name column: |
=FILTER(Table1[Name],Table1[Actual Age]>=40,"No record") |
The formula should be this for returning the reference column: |
=FILTER(Table1[Reference],Table1[Actual Age]>=40,"No record") |
2. Your use of 'rounddown' is correct here, simply replace cell reference with the formula originally in $D3.
Actual Age
=ROUNDDOWN(((TODAY()-[@[Day of Birth]]))/365.25,0) |
=ROUNDDOWN(((TODAY()-[@[Day of Birth]]))/365.25,0) |
=ROUNDDOWN(((TODAY()-[@[Day of Birth]]))/365.25,0) |
=ROUNDDOWN(((TODAY()-[@[Day of Birth]]))/365.25,0) |
I turned all of it into an official excel table so you'd see [@...] for the references, change it back if you like. If I misunderstood your questions, please let me know. Hope it helps!
Feb 18 2022 12:45 AM
@Robert1290 If your Excel version supports it you can use the FILTER function. Otherwise, a simple pivot table could do it. Both demonstrated in the attached file on Sheet2
Feb 18 2022 12:48 AM
Feb 18 2022 12:49 AM
Here is it with the comments. The pivot table solution is okay too but if you prefer formulas either way is okay.
Feb 18 2022 01:35 AM