Jan 13 2023 09:05 AM
Hi,
Can someone please help me in developing a formula/pivot that allows me to find the latest date based on some criteria. Below is the example Excel that is similar to what I am using:
Fund Name | Valuation Date | Data Received |
Fund 1 | 31/12/21 | Yes |
Fund 1 | 31/03/22 | Yes |
Fund 1 | 30/06/22 | Yes |
Fund 1 | 30/09/22 | Yes |
Fund 1 | 31/12/22 | No |
Fund 2 | 31/12/21 | Yes |
Fund 2 | 31/03/22 | Yes |
Fund 2 | 30/06/22 | Yes |
Fund 2 | 30/09/22 | No |
Fund 2 | 31/12/22 | No |
In the output I would expect the fund name and the latest date (where data received = yes) to be returned as per the below:
Fund Name | Valuation Date |
Fund 1 | 30/09/22 |
Fund 2 | 30/06/22 |
Any help would be greatly appreciated.
Thanks,
Danny
Jan 13 2023 09:12 AM
=LARGE(IF(($A$2:$A$11=E2)*($C$2:$C$11="Yes"),$B$2:$B$11),1)
You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or Excel 2021.
Jan 14 2023 09:47 AM