Forum Discussion

Edward_Ndambala's avatar
Edward_Ndambala
Copper Contributor
May 01, 2024
Solved

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...
excel
Formulas and Functions
office 365
  • HansVogelaar's avatar
    HansVogelaar
    May 02, 2024

    Edward_Ndambala 

    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

HansVogelaar's avatar
HansVogelaar
MVP
Jun 24, 2024

Edward_Ndambala 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

  • Edward_Ndambala's avatar
    Edward_Ndambala
    Copper Contributor
    Jun 26, 2024

    I have tried the formula and it works well for the samples I've checked. I've adjusted the formula a bit to give me the smallest or equal rather than the largest.

     

    Thank you for your help. I am continuing to verify the results but so far so good!!!

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jun 25, 2024

    Edward_Ndambala 

    Thanks! Do these do what you want?

     

    =INDEX([Net Asset Value], XMATCH(1, ([Scheme Name]=[@[Scheme Name]])*([Date]>=WORKDAY(EDATE($D12471, -12)+1, 1, Holidays)),-1))

     

    and

     

    =INDEX([Date], XMATCH(WORKDAY(EDATE([@Date], -12)+1, -1, Holidays),[Date], 1))

     

    Please check carefully!

Resources