Can this formula be optimized any further?

[Mr. Snrub]

I have the following formula that calculates yearly return:

=IF([@Date]>=TODAY(),NA(),IF(ROW()-ROW([#Headers])=1,0,LET(current_rows_year,YEAR([@Date]),prev_rows_year,YEAR(OFFSET([@Date],-1,0)),            prev_ytd_return,OFFSET([@[YTD Return]],-1,0),IF(current_rows_year<>prev_rows_year,[@[Daily Return]],(1+prev_ytd_return)*([@Daily Return]+1)-1))))

Expanded for readability:

=IF(
    [@Date]>=TODAY(),NA(),
    IF(
        ROW()-ROW([#Headers])=1,0,
        LET(
            current_rows_year,YEAR([@Date]),
            prev_rows_year,YEAR(OFFSET([@Date],-1,0)),
            prev_ytd_return,OFFSET([@[YTD Return]],-1,0),
            IF(
                current_rows_year<>prev_rows_year,[@[Daily Return]],
                (1+prev_ytd_return)*([@Daily Return]+1)-1
            )
        )
    )
)

Is there a way to optimize it any further? I was hoping to find some way to prevent using OFFSET, for one.

 

[Eric W]

I think it's as good as you're going to get. Clearly this formula is in a table. Tables have some issues, like not allowing Spill formulas, and not having a good way to reference previous or next row data. The OFFSET you use is the cleanest way to do it, volatility notwithstanding. I know some ways to use INDEX instead (using the same kind of logic as ROW()-ROW([#Headers]) ), but it would really complicate the formula, with probably little gain. OFFSET is fast, and unless you have some specific issues with your worksheet lagging, you'll probably never notice.

 

[djclements]

One option for referencing the row above is to define a relative named range in Name Manager. Since no sample data was provided, consider the following example...

Assuming Table1 is located on Sheet1 with "Date" in column A, "Daily Return" in column B, and "YTD Return" in column C, start by selecting any cell in row 2 (e.g. E2). Then, open Name Manager (Ctrl+F3) and define the following two names using an absolute column/relative row reference to $A1 and $C1 respectively:

PrevRowYear:
=IFERROR(YEAR(Sheet1!$A1),0)

PrevRowYTD:
=Sheet1!$C1

The calculated column formula can then be simplified as follows:

=IF([@Date]>=TODAY(),NA(),IF(ISREF(PrevRowYTD Table1[#Headers]),0,IF(YEAR([@Date])=PrevRowYear,(1+PrevRowYTD)*([@[Daily Return]]+1)-1,[@[Daily Return]])))

Please adjust the sheet/table names and column references as needed to coincide with your actual workbook structure.

 

[RoryA]

Your formula uses TODAY so will always be volatile anyway.

 

[Mr. Snrub]

Your formula uses TODAY so will always be volatile anyway.

On my real workbook that's actually a Named Range that points to a cell on another worksheet whose value is

=TODAY()

. So I probably should've mentioned that.

=IF([@Date]>=TODAY(),NA(),IF(ISREF(PrevRowYTD Table1[#Headers]),0,IF(YEAR([@Date])=PrevRowYear,(1+PrevRowYTD)*([@[Daily Return]]+1)-1,[@[Daily Return]])))

What does

ISREF(PrevRowYTD Table1[#Headers])

do? Are you sure that's correct? With the space between PrevRowYTD and Table1[#Headers]?

 

[Mr. Snrub]

never mind, that formula is correct. My workbook wasn't giving the correct values because I had the wrong row reference in the PrevYear and PrevYTDReturn ranges. But they work now! Thanks! This is great!

 

[djclements]

What does

ISREF(PrevRowYTD Table1[#Headers])

do? Are you sure that's correct? With the space between PrevRowYTD and Table1[#Headers]?

Space is the intersection operator, which returns the intersection point between two ranges. If the ranges don't intersect, it returns #NULL!. ISREF returns TRUE if they intersect, or FALSE if they do not. It was suggested as an alternative to the ROW()-ROW(Table1[#Headers])=1 method, but is not a game-changer by any means.

Glad you were able to figure it out and get it working in your actual workbook. Cheers!