Forum Discussion
EXCEL FORMULAS
Let's say you have a range named Holidays that lists public holidays.
And let's say you want to compare Net Asset Value (column C) with the previous year.
For row 2, the previous year's value is
INDEX($C$2:$C$5000, XMATCH(1, ($B$2:$B$5000=$B2)*($L$2:$L$5000=WORKDAY(EDATE($L2, -12)+1, -1, Holidays))))
So the increase/decrease is
=C2/INDEX($C$2:$C$5000, XMATCH(1, ($B$2:$B$5000=$B2)*($L$2:$L$5000=WORKDAY(EDATE($L2, -12)+1, -1, Holidays))))-1
Please show us what your data look like, with row numbers and column letters.
HansVogelaar, Here's my data. The missing date were either public holidays or weekends
Let's say you have a range named Holidays that lists public holidays.
And let's say you want to compare Net Asset Value (column C) with the previous year.
For row 2, the previous year's value is
INDEX($C$2:$C$5000, XMATCH(1, ($B$2:$B$5000=$B2)*($L$2:$L$5000=WORKDAY(EDATE($L2, -12)+1, -1, Holidays))))
So the increase/decrease is
=C2/INDEX($C$2:$C$5000, XMATCH(1, ($B$2:$B$5000=$B2)*($L$2:$L$5000=WORKDAY(EDATE($L2, -12)+1, -1, Holidays))))-1
- So do you advice that I have a separate sheet which includes public holidays and weekends?
Afterwards, I use the formula you’ve mentioned?
Could you show your ideal data structure with the calculations you’ve made?
