Resolving Circular Referencing

There are cases when a formula is dependent on the resultant cell of itself and we really need to keep circular reference. However the challenge is that using circular reference, excel converts the formula result to zero. Let's see how to resolve such a scenario.

To begin with. For let us understand the meaning of circular reference...

Circular Reference : When a formula refer back to its own cell either directly or indirectly, it is called as circular referencing.

Example : Let's take a simple example, a person invest an amount of 1 Lac in a Fixed Deposit for one year at an interest rate of 9%. So at the end of one year he gets an interest of Rs. 9000/-. This can be calculated in excel using IPMT function.

FD Plan
FD Amount	100000
Interest Rate	9%
Years	1
Interest Amount	₹ 9,000.00

The formula used to calculate Interest Amount is = IPMT(Interest Rate,1,Years,FD Amount)

              * Here 1 stand for the period for which interest is calculated

              * FD Amount is taken as negative value

Let's say there is an auto-renewal process and we need to add the interest amount back into the principal for the calculation of next year. This Excel will not allow prompting it as a circular reference error. 

Solution : To avoid such prompt message and to continue with the loop based calculations, we can enable the option of "Enable Iterative Calculations".

To enable this we need to follow the below mentioned path (Excel 2013)

File Tab --> Options --> Formulas --> Enable Iterative calculations

And we can now easily use circular referencing.

Sort Data along with pictures

Hi !

A yet another common problem that people face in their data is why pictures do not get sorted when we sort the data. Specially in product industry, where each and every depiction is via pictures, work become really tedious when it comes to task like sorting and filtering data.
What practically happens is data gets sorted while the placement of pictures gets distorted.

So here's a simple solution:

Before performing the task of Sort/Filter we need to ensure following things in our data:

1. Cells are not merged
2. Every individual picture appropriately fit in exactly one cell
3. For pictures, the property is set to "Move but don't size with cells". To do this you may right-click the picture , select "Size and Properties" option and change the property under "Properties" Tab. Below is the screenshot for reference.

            

Size and Properties window for picture

Now, try using filters or sort your data based on any field and you will notice pictures moving along with it.

Below is a capture of sample data file with ideal settings:

 

So, this is how the column that contains pictures should look like.

Hope this helps !!All the best !!