Microsoft Office Tutorials and References

In Depth Information

The problem with using number formats is that we can’t adjust them

dynamically. Therefore, we must adjust the data itself.

To adust the power of ten dynamically, set up your data in two

ranges. The first range contains your raw data. The second range

contains the modified data that you chart. Typically, the second

range is modified only with regard to the position of its decimal

points.

To illustrate, suppose your raw data is in a range named RawData.

You could rely on a cell named Scale that has a formula that’s

something like this:

=IF(MAX(RawData)<800000,.001,.000001)

This formula returns .001 if the data should be displayed as

thousands, and .000001 if it should be displayed as millions.

(There’s nothing magic about 800,000; you can use any value you

want.)

Formulas in your range of modified data would use this scaling

value to adjust automatically to the scale of your data. For example,

if cell C5 contains raw data, a cell with the adjusted data would

contain a formula like this:

=C5*Scale

Typically you format the modified data range using the custom

format: #,##0.0

With this setup:

•

If your raw data is: 1234567.89

•

Your modified data is: 1.23456789

•

With formatting, your modified data becomes: 1.2