Need help with Tables

[sandipgumtya365]

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

[#2]

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

[#3]

@sandipgumtya365 

That's like

=SUMPRODUCT((HomeLoan[[Months]:[Months]]>0)*(HomeLoan[[Months]:[Months]]<=I1*12)*HomeLoan[[Interest Paid]:[Interest Paid]])

[#4]

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

[#5]

@Craig Hatmaker 

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.

[#6]

@SergeiBaklan The formula in E2 is right but in E3, a slight modification is required and then can be dragged. As it is there is your soln, is it the way for absolute reference in a table? I think, if it was not a table then it won't have been difficult. But as I would require a few more data to be added, so the table is required.

[#7]

@sandipgumtya365 

If under absolute reference you mean to fix the column, when use Table1[[A]:[A]] instead of Table1[A].

If to fix the row - no, it's not with structured references. You may use cell references, OFFSET(), INDEX() depends on situation and which exactly references are required.

[#8]

@sandipgumtya365 Don't drag (also known an fill). Use copy/paste.  I've used this method since 2010. It works. Just copy first cell, select other cells, then paste. Simple.

Sergei Baklan Yes - you answered his question and the structured reference you suggest is more complicated.  So which is better? Complicated references with drag or simple reference with copy/paste? Or best - PIVOTTABLES! (See attached sheet Pivots). 

@sandipgumtya365 I urge my staff to use tables properly because they save time and make models more flexible and self documenting. I also urge my staff to use PivotTables because they are absolutely awesome.  Take 15 minutes to learn them. You'll love them. 

[#9]

@Craig Hatmaker , @SergeiBaklan Thanks. 

Tried the copy+paste instead of Drag, it worked. Worked with SUMIFS too.

Now, I wanted to present the data in graphs using PIVOT. One issue is with the zero value cells. As the number of years i.e, the tenure of the loan is a variable quantity, I want the graph to adapt to it. Basically no zero values in the graph. As you can see a steep fall, is unwanted. The years in the X-axis should be as per the loan tenure. If I change it to 10, the X-axis should reflect the same. How should I do this?

[#10]

@sandipgumtya365 There are several ways to achieve this:

1) Manual method on Table

2) Manual method on Pivot

3) Automated with Power Query

4) Automated with VBA

All but #4 requires Data > Refresh All

To get my answer:
1) Close this question and mark as answered by me

2) Select the method you prefer

3) Ask that question

4) Email me at Craig_Hatmaker@Yahoo.com to make sure I see your question

Today is a holiday so I'll be checking email infrequently. Our time zones are probably different. I'm on the east coast of the US so it is morning here now and will be up for the next 12 hours.

[#11 · Accepted solution]

@sandipgumtya365 

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 Result

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

[#12]

@sandipgumtya365 

By the way, another variant of formula here

could be

=SUM(INDEX(Home_Loan[Interest Paid],SEQUENCE(12,1,(J16-1)*12)+1))

[#13]

@SergeiBaklan Thanks a lot. I think my issue is resolved now. Than you. btw, I want to learn DAX, What is the best source or channel to go about. Pl suggest.

Thanks

Sandip.

[#14]

@sandipgumtya365 , you are welcome, glad to help