Forum Discussion
Need help with Tables
Hello EXCELers,
Need some help with a formula. I was preparing a loan amortization table where I wanted to get the yearly interest paid. I have used the Sumproduct function to get the yearly data. The first 12 months was calculated correctly but in year-2, the reference columns are shifting which is undesired. There may be further data added, so data must in table format. I want the data in the attached order. Can someone help with how to do absolute referencing in tables? Are there any other formulas to do this in an easier way?.
Thanks
Sandip Gumtya
Creating PivotTable add data to data model and instead of direct aggregation (sum) use measures like
Paid Interest:= VAR total=SUM(Home_Loan[Interest Paid]) VAR Result=IF(total=0,BLANK(),total) RETURN ResultResult will be like
As a source I took the table to the left. As a comment, please don't add totals manually, Table support total row you may switch on/off and use the same formulas. Otherwise that could be side effect.
sandipgumtya365 There are many ways to approach this. I think the main problem you are having is how Excel uses fill with tables. Don't use fill for this. Copy the first cell and paste to other cells to preserve the table reference.
I would also encourage using SUMIFS() instead of SUMPRODUCT. They both work but this version will copy/paste right correctly.
=SUMIFS(HomeLoan[Interest Paid],HomeLoan[Months], ">" & (E1-1)*12,HomeLoan[Months], "<=" & E1*12 )
That's like
=SUMPRODUCT((HomeLoan[[Months]:[Months]]>0)*(HomeLoan[[Months]:[Months]]<=I1*12)*HomeLoan[[Interest Paid]:[Interest Paid]])SergeiBaklan
I guess we should point out that there are major differences in the results, The SUMPRODUCT() versions are calculating cumulative results. The SUMIFS() version is calculating for each year. So for comparison, the cumulative version of SUMIFS would be:=SUMIFS(HomeLoan[Interest Paid],HomeLoan[Months], "<=" & E1*12 )
Again, while SUMPRODUCT can also be simplified and work, SUMIFS, in my opinion, better describes the calculation - it is "Summing" numbers "If" they match criteria. I feel it is a best practice to let formulas self-document as best we can.
I'd also highly recommend using PivotTables instead of formulas. Pivots are much easier and to which we can effortlessly add timeline slicers and charts if we so choose.Craig, perhaps you are right with business logic, I have no idea. I only answered on question how to keep structured references unchanged ("absolute") if drag the formula in E2 to the right. Assuming in E2 is correct formula.
