I'm in the process of creating my own mortgage calculation spreadsheet. I've gotten pretty far along and I'm mostly happy with how it works, but I stumbled on a spreadsheet created by someone else and I can't figure out how they set up one of their formulas. At the top of the sheet they have a cell that shows the Total Interest Paid. Below that they have a column that has an "INTEREST" label and shows all the individual interest payments over the term. Somehow they get the total of all the values in this column to show up in that cell at the top. I can't see how they did it.
FWIW The individual interest payments range from "I16:I314". The entry in the cell in question is: "=@TotalInterest" (I'm pretty sure when I looked at this on my computer at work it did not have the @ sign.) I just read the help note from Microsoft on "Implicit intersection operator: @ " and I basically get the idea of what they're saying, but it doesn't answer my question as to how entering "=@TotalInterest" or "=TotalInterest" into a cell provides the sum from a column. I checked to see if they had created a named table and they had, but the table was the entire table including all columns and is named "PaymentSchedule" not "TotalInterest".
I thought I could make the same thing work by entering "=SUM(I16:I)" into the same cell, but it gave me a "Name" error. I've used that syntax with array formulas before, so I don't know why it didn't work here, but I changed it to "=SUM(I16:I360)" and it seems to work fine. So I have a solution that works for me, but I would really like to know what kind of voodoo was used in this sheet I found on the net.
FWIW The individual interest payments range from "I16:I314". The entry in the cell in question is: "=@TotalInterest" (I'm pretty sure when I looked at this on my computer at work it did not have the @ sign.) I just read the help note from Microsoft on "Implicit intersection operator: @ " and I basically get the idea of what they're saying, but it doesn't answer my question as to how entering "=@TotalInterest" or "=TotalInterest" into a cell provides the sum from a column. I checked to see if they had created a named table and they had, but the table was the entire table including all columns and is named "PaymentSchedule" not "TotalInterest".
I thought I could make the same thing work by entering "=SUM(I16:I)" into the same cell, but it gave me a "Name" error. I've used that syntax with array formulas before, so I don't know why it didn't work here, but I changed it to "=SUM(I16:I360)" and it seems to work fine. So I have a solution that works for me, but I would really like to know what kind of voodoo was used in this sheet I found on the net.
