Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 1
Default Solution for charting IF statements that return blank values

Hi everyone,

I know a big issue for people is that when you use an IF formula to
return a blank cell and graph it, it returns a zero value, for example
if(a10,a1,"") returns a blank cell that charts as a zero.

There is a solution to this problem that does not involve the more
common returning of an NA() value and conditionally formatting it out.

The solution is the use of dynamic ranges to set the chart values. It
is relatively easy and good practice as you can easily update and
reference data on other sheets. What you do is define a named range
for your data which dynamically extends with your information.

Step 1:
From the top file menu's select Insert Name Define.


Step 2:
Give it a name that you will remember, that cannot start with a number.

Step 3:

Define your range using the following formula:

=$b$17:INDEX($b$17:$m$17,1,MAX(IF($b$17:$m$17<"", COLUMN($b$17:$m$17)))-COLUMN($b$17)+1)

But replace $b$17 with the FIRST cell in the row, and replace $m$17
with the LAST cell in the row. (Make sure you use absolute references
($) otherwise it will not work!).

NB: If your data is in columns, not rows, you can reverse the last two
formulas in the INDEX formula and use the ROW function instead of
COLUMN. (ie, the INDEX formula uses row and then column -
INDEX(array,row_num,column_num)). Please post if you would like a ROW
formula posted.

Step 4:

In your chart, right click, select Source Data, select the series you
wish to set dynamically, and in the Values box enter ='[Sheet
name]'![Named range from Step 2].

Good luck!

  #2   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 6,582
Default Solution for charting IF statements that return blank values

This is the preferred approach, when the "blanks" occur at either end of the
range. When they occur in the middle, you have fewer options.

Another approach is to have two data ranges, one for viewing tabular output,
and one laid out for efficient charting. The former links to the original
data with "", the latter with NA(). The benefits of this include flexibility
in layout and format of the tabular output, without worrying about getting
it to plot the way you want; not requiring CF to hide errors in the table;
not hiding the errors in the chart data, so you can more readily debug data
issues; and more. The two data regions can be linked to each other, or
preferably to a third main data sheet.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


wrote in message
oups.com...
Hi everyone,

I know a big issue for people is that when you use an IF formula to
return a blank cell and graph it, it returns a zero value, for example
if(a10,a1,"") returns a blank cell that charts as a zero.

There is a solution to this problem that does not involve the more
common returning of an NA() value and conditionally formatting it out.

The solution is the use of dynamic ranges to set the chart values. It
is relatively easy and good practice as you can easily update and
reference data on other sheets. What you do is define a named range
for your data which dynamically extends with your information.

Step 1:
From the top file menu's select Insert Name Define.


Step 2:
Give it a name that you will remember, that cannot start with a number.

Step 3:

Define your range using the following formula:

=$b$17:INDEX($b$17:$m$17,1,MAX(IF($b$17:$m$17<"", COLUMN($b$17:$m$17)))-COLUMN($b$17)+1)

But replace $b$17 with the FIRST cell in the row, and replace $m$17
with the LAST cell in the row. (Make sure you use absolute references
($) otherwise it will not work!).

NB: If your data is in columns, not rows, you can reverse the last two
formulas in the INDEX formula and use the ROW function instead of
COLUMN. (ie, the INDEX formula uses row and then column -
INDEX(array,row_num,column_num)). Please post if you would like a ROW
formula posted.

Step 4:

In your chart, right click, select Source Data, select the series you
wish to set dynamically, and in the Values box enter ='[Sheet
name]'![Named range from Step 2].

Good luck!



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using COUNTIF to search for existence Epinn Excel Worksheet Functions 31 October 27th 06 04:57 AM
Return Numeric Values Matching EXACT Date for Criteria Sam via OfficeKB.com Excel Worksheet Functions 4 October 20th 06 11:20 PM
VLOOKUP RETURN BLANK dzierzekr Excel Worksheet Functions 3 September 15th 06 08:52 PM
Return Summed Count of Multiple Consecutive Numeric Values Sam via OfficeKB.com Excel Worksheet Functions 4 April 10th 06 10:35 PM
Count Intervals of 2 Consecutive Values in same Row and Return Count across Row Sam via OfficeKB.com Excel Worksheet Functions 6 November 29th 05 04:27 PM


All times are GMT +1. The time now is 03:51 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"