zach3ZachPremium
join:2000-05-04
Saint Louis, MO Reviews:
 ·AT&T Yahoo
·AT&T Southwest
| [Excel] Formula Question I have a spread sheet that has a formula in it like the one shown below:
=IF(A1>0,A1,"")
I have the above formula in Cell D1 and want to copy it down the column.
The problem occurs when I want to use the column in a chart because I want the chart to ignore any information in Cell unless it is the exact number from A1.
Presently when I copy the formula down the column it results in the chart showing "0" and expanding across the "X" axis.
Is there a way to eliminate this problem?
Thanks,
Zach |
|
jsnwtlrPremium
join:2004-12-16
Carrollton, TX | Use the formula:
=IF(A1>0,A1,NA())
The NA function should cause the chart to ignore the cell. |
|
|
|
zach3ZachPremium
join:2000-05-04
Saint Louis, MO | JSN,
I thought that I had tried that before. Anyway, it doesn't work.
The result is shown as a #N/A in the cell and it also shows up in the chart exactly as shown in the cell.
Thanks,
Zach |
|
jsnwtlrPremium
join:2004-12-16
Carrollton, TX | Can you post a small sample of the spreadsheet including the problem chart? I guess I don't understand the problem. |
|
chipshotThe More I Learn, The Less I Know
join:2001-11-07
Louisville, KY | reply to zach3
I'm pretty sure there's no way to make a graph skip a cell just because it's not a value. But you can use something like the technique in this file and graph the "Filtered Data"
--
Eschew obfuscation. |
|
zach3ZachPremium
join:2000-05-04
Saint Louis, MO Reviews:
·AT&T Yahoo
·AT&T Southwest
| reply to zach3
I have uploaded a file where I attempt to explain what I am trying to do. Everything is updated from a pivot table and I am attempting to update the chart also.
Do to the positive/negative values on the chart I do not know how to do this using the pivot table charting capabilities nor do I even know it can be done that way.
Anyway here is something that you can play with and see what you can do with it.
I have tried just about everything that I can think of except nesting an "if" statement with an "and" statement which I am not sure how to do.
Thanks to both of you for your assistance.
Zach |
|
| Try,
worksheet cell formula =IF(A1>0,A1,0)
In the chart
Click the data series
Format>Selected Data Labels
Category>Custom
Type: #;-#;"" |
|
chipshotThe More I Learn, The Less I Know
join:2001-11-07
Louisville, KY | reply to zach3
Why aren't you just graphing what comes out of your pivot table? I don't understand why you're splitting the positives and negatives into separate columns. Graphing the positives and negatives as a single range usually makes the most sense.
If you're trying to get the negative bars to show up as different colors, there's a way to do that, but it's kind of complicated and it will make your spreadsheet much less intuitive to update.
--
Eschew obfuscation. |
|
zach3ZachPremium
join:2000-05-04
Saint Louis, MO Reviews:
·AT&T Yahoo
·AT&T Southwest
| reply to xlhth
xlhth,
Thanks, that seems to solve the problem of the expanding chart just fine!!
I am using Office 2003 and I can't seem to find the The data series in the format you are asking me to try. I can find Format>Data Labels but not the term "Selected". I can find Category name but not "Custom"
The problem is solved except that I end up with zeros showing in the chart and I would prefer that they didn't show.
One step at a time!
Zach |
|
zach3ZachPremium
join:2000-05-04
Saint Louis, MO | reply to chipshot
Chipshot,
Yes, I am wanting the bars to be different colors that I why I choose this method to make the chart. You are very correct it is an involved process because in my final chart format the chart is dynamic.
Thanks,
Zach |
|
| oops
In the chart
Click on one of the values (4170)to highlight the data series labels
Menu Bar > Format > Selected Data Labels
Number Tab > Category > select Custom
enter in Type: #;-#;""
This formats the chart label not to display zero values
For more info, see "Create or delete a custom number format" in Help |
|
zach3ZachPremium
join:2000-05-04
Saint Louis, MO | xlhth,
Works like a champ!! 
Thanks,
Zach |
|
