SOLVED

# EXCEL FORMULAS

Copper Contributor

# 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.

10 Replies

# Re: EXCEL FORMULAS

Please show us what your data look like, with row numbers and column letters.

# Re: EXCEL FORMULAS

@HansVogelaar, Here's my data. The missing date were either public holidays or weekends

best response confirmed by Edward_Ndambala (Copper Contributor)
Solution

# Re: 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

# Re: EXCEL FORMULAS

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?

# Re: EXCEL FORMULAS

You don't have to list weekend days - the WORKDAY formula will take care of that. If your weekend days are not Saturday/Sunday, you can use WORKDAY.INTL.

The range that lists public holidays can be on the same sheet as the data or on another sheet, whichever is more convenient to you. After entering the holidays, select the range, then click in the name/address box on the left hand side of the formula bar, type Holidays and press Enter.

The formula that I posted can be used in a cell in row 2, then filled down.

# Re: EXCEL FORMULAS

@HansVogelaarThank you for the solution you gave me. It works for a differences in days but i've seen that my data has up to 3 days of no data. I've tried modifying the formula but i'm stuck. I was asking if you can assist in modifying the formula to include up to 3 days instead of 1.

I have attached what happens to my data with 1 day and the formulas i've used.

To get NAV: =INDEX([Net Asset Value], XMATCH(1, ([Scheme Name]=[@[Scheme Name]])*([Date]=WORKDAY(EDATE(\$L2, -12)+1, -1, Holidays))))

To get date of the NAV: =INDEX(Raw_Data[Date], XMATCH(WORKDAY(EDATE(Current_Date, -12)+1, -1, Holidays),Raw_Data[Date]))

# Re: EXCEL FORMULAS

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?

# Re: EXCEL FORMULAS

@HansVogelaarI have attached the sample size as requested.

# Re: EXCEL FORMULAS

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))

# Re: EXCEL FORMULAS

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!!!

1 best response

Accepted Solutions
best response confirmed by Edward_Ndambala (Copper Contributor)
Solution

# Re: 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