zach3
Zach
Premium
join:2000-05-04
Saint Louis, MO
clubs:
| reply to zach3
Re: [Excel] Formula Question
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 |
|
xlhth
@optonline.net | Try,
worksheet cell formula =IF(A1>0,A1,0)
In the chart
Click the data series
Format>Selected Data Labels
Category>Custom
Type: #;-#;"" |
|
chipshot
The More I Learn, The Less I Know
join:2001-11-07
Louisville, KY
clubs:
| 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. |
|
zach3
Zach
Premium
join:2000-05-04
Saint Louis, MO
clubs:
| 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 |
|
zach3
Zach
Premium
join:2000-05-04
Saint Louis, MO
clubs: | 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 |
|
xlhth
@optonline.net
| 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 |
|
zach3
Zach
Premium
join:2000-05-04
Saint Louis, MO
clubs: | xlhth,
Works like a champ!! 
Thanks,
Zach |
|
