Forum Discussion
EXCEL FORMULAS
Hey everyone.
I have daily data which excludes weekend and public holidays. When i want to do an annual percentage change calculation, one of the dates could possibly be weekend or public holiday date so there won't be any data. It returns an error. I want to be able to have an excel formula that if the current and last year date have a number, the percentage change calculation should normally happen. If not the last date with the data should be used as the substitute date. For example, if the last year date is a Saturday and there was data on Friday then the Friday data should be used.
I hope i can get guidance on what to use formula wise.
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
