Formula to calculate new worth based on today's date

This problem was solved by a series of INDEX / MATCH applied to static named ranges.

First I applied the name Month_List to cells A9:20 and then applied the names A1_list, A2_list and A3_List to columns containing the amount.

Then I used helper cells in column J - I left them there to "show my work." Cell J3 contains the formula: =TEXT($F$3,"mmm") - this converts the current date into a month name.

Cell J4 =MATCH($J$3,Month_List,0) - this finds the row on which the month is found in the named range Month List.

Cell J5 =INDEX(A1_List,J4) - this finds the dollar value for Asset 1 on the same row with the month. Cells J6 and J7 do the same thing only for the other two assets.

Finally, Cell J8 =J5+J6+J7 or the sum of the three assets for the indicated month.

I put this formula into Cell F5 and then kept substituting the subformulas until I got =INDEX(A1_List,MATCH(TEXT($F$3,"mmm"),Month_List,0))+INDEX(A2_List,MATCH(TEXT($F$3,"mmm"),Month_List,0),1)+INDEX(A3_List,MATCH(TEXT($F$3,"mmm"),Month_List,0),1)

You can remove Column J and this formula will still work.

This is a case where I recommend using helper cells. It makes it easier to see the logic and debug it if something goes wrong. Also note that the Match function is called three times to do the same calculation. It's not a big deal here, but could become an issue in a much larger spreadsheet.

Try

Formula:

Please Login or Register  to view this content.