Dynamic Inter-Cell Computations

Comparison

Comparison = Amount Payable of the Current Month - Amount Payable of January.

Each value extended from the cell is compared with the first value. In the example, the difference is calculated.

Proportion

Proportion = Amount Payable of the Current Month/Amount Payable of All Months.

The proportion that each value extended from the cell occupies in the total amount is calculated.

MoM

The ratio of the data of each row to the data of the previous row is calculated.

MoM (Development Speed) = Amount Payable of the Current Month/Amount Payable of   the Previous Month.

B2

Amount Payable of the Current Month

B2[A2:1]

Amount payable of January. That is to say, the value of cell B2 corresponding to the first value of column A2 is returned.

B2 – B2[A2:1]

Amount Payable of the Current Month – Amount Payable of January

B2

Amount Payable of the Current Month

B2[!0]

All values (3949.70, 2293.65, 2310.21, 2116.69, 1346.74, and 4780.93) extended from cell B2 are returned.

For details, see Common Formulas of Hierarchical Coordinates.

SUM(B2[!0])

All the returned values of cell B2 (namely the total amount payable of all months) are summarized.

B2/SUM(B2[!0])

The proportion of the monthly amount payable to the total amount is calculated.

&A2

The position of each value expanded from cell A2 is obtained.

B2/B2[A2:-1]

B2[A2:-1]: The amount payable of the previous month is returned.

B2/B2[A2:-1]: Amount Payable of the Current Month/Amount Payable of the Previous Month

IF(&A2 > 1, B2/B2[A2:-1], 0)

Use the formula if it is not the cell with the serial number 1 (namely not the first cell). Output 0 if it is the first cell.

Layer-by-Layer  Accumulation

Group by year, and calculate the cumulative total of the amounts payable of each month of each year.

In the group report, the data of each group is accumulated row by row.

Cross-Layer Accumulation

The cumulative total of the amounts payable of all months and years is calculated.

In the group report, all the data is accumulated row by row. The data continues to be accumulated based on the result of the previous group during the shift of groups.

Conditional Summary

The data that meets the specified conditions is summarized.

A1   to E1

Enter the field title names in order (Year, Month, Amount Payable, Layer-by-Layer Accumulation, and Cross-Layer Accumulation) in cells. Select A1 to E1, and select Header under Style > Style Setting > Theme.

A2

Drag the Year data column in the data set into the cell.

B2

Drag the Month data column in the data set into the cell.

C2

Drag the Amount Payable data column in the data set into the cell. Select Summary from the drop-down list of Data Setting under Cell Element, as shown in the following figure.

D2

Enter the formula C2 + D2[B2:-1]. The data expanded from cell C2 is cumulatively summarized row by row according to the year.

E2

Enter the formula IF(&B2 > 1, C2 + E2[B2:-1], C2 +   E2[A2:-1,B2:!-1]). The data expanded from cell C2 is cumulatively accumulated row by row. The data continues to be accumulated based on the cumulative result of the previous year during the shift of the year. Among the formula, B2:!-1 means acquiring the value of the last row of column B2 in the group. If it is B2:!-2, it means acquiring the value of the last but one row of column B2.

A3 to C3

Merge cells A3 to C3,  and enter the field title: Number of   Months with the Amount Payable Greater Than 2500. Set the left parent cell to A2, and select Header under Style > Style Setting > Theme.

D3 to E3

Merge cells D3 to E3,  and enter the formula COUNT(C2[!0]{A2 = $A2 && C2 > 2500}) to count the number of months with the amount payable greater than 2500.

D2[B2:-1]

The previous value of the layer-by-layer accumulation column of the current year is returned each time. For example, for January 2010, 0 is returned. For February 2010, the value of D2 (2837.94) corresponding to January is returned.

C2 + D2[B2:-1]

Amount Payable of the Current Month and Year + Value of the Cell of the Previous Layer-by-Layer Accumulation Column

&B2

The position of each value expanded from cell B2 is obtained. 

Note: The position of B2 is cumulatively counted from the beginning   according to the year of the left parent cell.
For example, for January 2011, the returned value is 1. For January 2010, the returned value is also 1.

For details, see Common Formulas of Hierarchical Coordinates.

E2[B2:-1]

The previous value of the layer-by-layer accumulation column of the current year is returned each time.

For example, for January 2010, 0 is returned. For February 2010, the value of E2 (2837.94) corresponding to January is returned.

For details, see Relative and Absolute Coordinates.

E2[A2:-1, B2:!-1]

For the data of each January, the cross-layer data of the last month of the previous year is returned.
For example, the cross-layer accumulation data of January 2011 should be returned with the cross-layer accumulation data (15631.50) of June 2010.

IF(&B2 > 1, C2 + E2[B2:-1], C2 + E2[A2:-1,B2:!-1])

If it is not the first month of each year, the returned value is the sum of the previous value of the layer-by-layer accumulation column of the current year and the amount payable of the current month. If it is the first month of each year, the returned value is the sum of the layer-by-layer accumulation data of the last month of the previous year and the payable amount of the current month.

A2 = $A2

$A2 retrieves the value at the corresponding position after cell A2 is expanded.

Determine whether the value of A2 is equal to the values expanded from A2.

That is to say, judge whether they are in the same year (considering that the calculations are required to be performed by year).

A2 = $A2 && C2 > 2500

The conditions that they are within the same year and the amount payable for each month is greater than 2500 are satisfied at the same time.

C2[!0]{A2 = $A2 && C2 > 2500}

The amount that satisfies the conditions of being within the same year and having an amount payable greater than 2500 for each month is obtained.

For example, the values returned for the year 2010 are 2837.94, 3596.13, 2822.19, and 2580.17.

COUNT(C2[!0]{A2 = $A2 && C2 > 2500})

The returned amounts payable are counted.