Microsoft Office Tutorials and References

In Depth Information

**Summing Formulas**

Figure 7-12:
The chart displays a histogram; the contents of cell E1 determine the number of categories.

The workbook
adjustable bins.xlsx
, shown in Figure 7-12, is available on the

companion CD-ROM.

The chart uses two dynamic names in its SERIES formula. You can define the name
Categories

with the following formula:

=OFFSET(Sheet1!$E$4,0,0,ROUNDUP(100/BinSize,0))

You can define the name
Frequencies
with this formula:

=OFFSET(Sheet1!$F$4,0,0,ROUNDUP(100/BinSize,0))

The net effect is that the chart adjusts automatically when you change the
BinSize
cell.

See Chapter 17 for more about creating a chart that uses dynamic names in its SERIES

formula.

Summing Formulas

The examples in this section demonstrate how to perform common summing tasks by using

formulas. The formulas range from very simple to relatively complex array formulas that compute

sums of cells that match multiple criteria.