Search This Blog

Monday, January 5, 2015

Ms. Excel , INDIRECT function for dynamic range

INDIRECT Formula used to Total a Dynamic Range of Values

This example is based on the data shown in the image above.

The SUM - INDIRECT formula created by using the tutorial steps below is:

=SUM(INDIRECT("D" &E1& ":D" &E2))

In this formula, the nested INDIRECT function's argument contains references to cells E1 and E2. The numbers in those cells, 1 and 4, when combined with the rest of INDIRECT's argument, form the cell references D1 and D4.

As a result, the range of numbers totaled by the SUM function is the data contained in the range of cells D1 to D4 - which is 50.

By changing the numbers located in cells E1 and E2; however, the range to be totaled can be easily changed.

This example will first use the above formula to total the data in cells D1:D4 and then change the summed range to D3:D6 without editing the formula in cell F1.

No comments:

Post a Comment