Posts Tagged 'Excel 2007'

Adding a Secondary Axis to an Excel 2007 Chart

Posted by: Mannie on: December 3 2009 • Categorized in: Excel

On some charts, you may want to plot a data series that contains different numbers on a different scale.  For example, you may want to show monthly sales figures and percentages on the same chart.  If you use the same scale for both data series, the percentages will be too small to show up on the chart.

To plot a data series on a secondary axis:

  1. Create your chart.
  2. Select the smaller data series by clicking the drop-down list on the Layout tab of the Chart Tools ribbon.
  3. Click the Format Selection button.
  4. Excel will display the Format Data Series dialog box
  5. In the Series Options category, select Secondary Axis.
  6. Click the Close button.
  7. Excel will plot the data series on a secondary axis.

Hiding the #DIV/0! Message

Posted by: Administrator on: January 29 2007 • Categorized in: Excel

If you are working with a formula that may refer to a blank cell or otherwise generate an error message, you can use a combination of the IF-THEN-ELSE and the ISERROR function to handle the #DIV/0! or other error messages that may appear.

The IF-THEN-ELSE has 3 parts: the condition, the TRUE condition, the FALSE condition. The following formula tests A1 for the number 100. If it is found, the cell displays the contents of A1. If it is not found, it multiplies another cell (B1) by 2:

=IF(A1=100,A1,B1*2)

By using one of the ISERROR functions, you can test for an error condition and use the IF statement to display a message or nothing at all. The common IS functions are ISERR, which returns TRUE for any error value except #N/A and ISERROR, which returns TRUE for any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!).

Suppose a cell is going to contain the average of several cells. If the cells are empty, the result will be division by zero and the #DIV/0! error will appear. The following formula will take care of this condition:

=IF(ISERR(AVERAGE(A1:A10)),”No Data”,AVERAGE(A1:A10))

If the formula AVERAGE(A1:A10) returns an error (most likely because there are no numbers in the range), the cell will display the words “No Data”. If there is no error, the cell will display the results of AVERAGE(A1:A10).