I. Overview
A Circular Reference occurs when the value calculated using the data in the previous row is used as the value for the current row. This document introduces how to implement a circular reference through hierarchical coordinates.
II. Steps
1. Prepare a dataset
The embedded dataset [ds1] of the template at the end of the document can be used. The dataset shows inbound and outbound stock of a certain warehouse in June.
2. Design report format
a) Drag the data column of [ds1] to B3, D3, and E3 respectively.
b) Type the function of IF(&B3=1,0,C3[B3:-1] + D3[B3:-1] - E3[B3:-1]) in C3 to enable a circular reference.
Component of function | Meaning |
&B3=1 | To determine whether the current data is the first one. If yes, the value of function is equal to 0, which means the initial stock is 0. If not, the initial stock is to be calculated using C3[B3:-1] + D3[B3:-1] - E3[B3:-1]. |
C3[B3:-1] + D3[B3:-1] - E3[B3:-1] | C3[B3:-1] represents the initial stock of the previous day, D3[B3:-1] represents the inbound stock of the previous day, and E3[B3:-1] represents the outbound stock of the previous day. The initial stock of the current day is calculated by adding the initial stock of the previous day and the inbound stock of the previous day and subtracting the outbound stock of the previous day. |
c) Preview
3. Validation
a) The initial stock of the current day should be equal to the final stock of the previous day. To validate, type the function of IF(&B3=1, D3 - E3, C3 + D3 - E3) in F3 to calculate the current stock.
Component of function | Meaning |
&B3=1 | To determine whether the current data is the first one. If yes, the final stock is calculated using D3 - E3, i.e., inbound stock minus outbound stock. If not, use C3 + D3 - E3 to get the final stock. |
C3 + D3 - E3 | The final stock of the current day is calculated by adding the initial stock of the current day and the inbound stock and subtracting the outbound stock of the current day. |
b) As shown in preview, the initial stock of the current day is equal to the final stock of the previous day.