Solved Hiding Data in Excel Chart

October 5, 2011 at 13:52:59
Specs: Windows XP
I am working on a graph that shows a daily percentage. However, I have a formula to populate the percentage across each day. So, the chart is showing the percentage for the last day that has data all the way across to the end of the month. Is there a way to hide those days, but still keep the axis showing the whole month?


See More: Hiding Data in Excel Chart

Report •


✔ Best Answer
October 12, 2011 at 10:07:56
When using Nested IF's, you just need to make sure you keep your value_if_true and value_if_false arguments straight.

I can't access your spreadsheet from work, so I can't test this with your data, but it works fine in a new spreadsheet.

Basically, your orginal formula represents the value_if_true for IF(F9="X",...

If that IF is true, then it evaluates your percentage. If it's not (i.e. there is no X in F9) it returns the value_if_false which is 0.

=IF(F9 = "X", IF(ISERROR(1-(F8+F7)/F6),0, (1-(F8+F7)/F6)), 0)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
October 5, 2011 at 19:46:37
Can you force your formula to evaluate to 0 for days when there is no data then plot zeros until the end of the month? That way the axis should still show the whole month.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
October 7, 2011 at 07:37:48
I'm not sure what I would need to add to make it a zero for the future days.I have my formula set to calculate based on a cummlative tally each day. So, here is my formula:

=IF(ISERROR(1-((F8+F7)/F6)),0, (1-((F8+F7)/F6)))

Where:
F6=E8+F5
F7=E9+F6
F8=E10+F7



Report •

#3
October 7, 2011 at 10:06:42
Well, this won't help answer your question, but I'll point out that you have some extra parentheses in your formula.

=IF(ISERROR(1-((F8+F7)/F6)),0, (1-((F8+F7)/F6)))

can be written as:

=IF(ISERROR(1-(F8+F7)/F6),0, (1-(F8+F7)/F6))

That said, I don't think you've supplied enough information for us to help.

All I see is a single formula. What are you graphing?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
October 7, 2011 at 10:14:38
1 2 3 4 5 6
Row: 3 Number of A's 0 0 5 6 0 0
Row: 4 Number of B's 0 0 1 4 0 0
Row: 5 Number of C's 0 0 0 0 0 0
Row: 6 Cumulative A 0 0 5 11 11 11
Row: 7 Cumulative B 0 0 1 5 5 5
Row: 8 Cumulative C 0 0 0 0 0 0
Row: 9 Percentage 0% 0% 80% 55% 55% 55%

Here is a section of the spreadsheet. I am graphing the percentage by day. And the formula above is row 9.


Report •

#5
October 7, 2011 at 10:43:05
Please click on the following line and repost your data after reading the instructions found via that link. Thanks!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
October 7, 2011 at 13:02:15
            	1	2	3	4	5	6	7	8
Number of A's	0	0	5	6	0	0	0	0
Number of B's	0	0	1	4	0	0	0	0
Number of C's	0	0	0	0	0	0	0	0
Cumulative A	0	0	5	11	11	11	11	11
Cumulative B	0	0	1	5	5	5	5	5
Cumulative C	0	0	0	0	0	0	0	0
Percentage	0%	0%	80%	55%	55%	55%	55%	55%


Report •

#7
October 7, 2011 at 16:45:31
It would have helped if you had included Column letters and Row numbers...

Keep in mind that we can't see your spreadsheet from where we're sitting nor can we read minds. The more detail that you provide the more detail we have to work with.

Can I assume that the text is in Column A?

Can I assume that the numbers 1 - 8 are in Row 2, since you said the percentage formulas were in Row 9?

What do the numbers 1 - 8 represent? Days of the month?

What is the Data Range for your chart?

Is there anything else you think we need to know so that we can offer quality suggestions?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
October 10, 2011 at 07:48:04
Sorry. The numbers (1-8 here) represent the days of the month and they go all the way out to 31 starting in row 2, column C. The data range is the percentage row (row 9) columns C-AG and is calculated with the formula above (the formula above is from column F). This formula is cumulative and for the future days that do not have data yet the percentage (row 9) is showing the monthly cumulative percentage (in this case 55%) which plots the rest of the way across. So, without changing the data range each day, is there a way to graph this without plotting the future days?

Report •

#9
October 10, 2011 at 12:53:50
I'm still confused.

I set up a sheet with your example data and formula. I placed your formula in F9 and then dragged it back to C9 and out to AG9.

When I graphed C9:AG9, I got data points at:
Day 3 - 80%
Day 4 - 55%
Day 5 - 55%
Day 6 - 55%
Day 7 - 55%
Day 8 - 55%.

All other "Days" (1 and 2, 9 thorugh 31) show nothing on the chart.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#10
October 10, 2011 at 13:17:11
Hmm...is there a way to post the spreadsheet here for you to look at?

Report •

#11
October 10, 2011 at 13:48:14
No, you can't post a file here.

If you can find a hosting site where you can post the file and then post the link here, that would work. I've seen that done in this forum in the past.

If you can't find a hosting site (but you have to try first!) I might be nice enough to provide an email address via Private Message so you can send the file directly to me.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#12
October 11, 2011 at 07:44:39
I haven't been able to find a hosting site that I can access from my work computer. Would you be willing to pm me your email address?

Report •

#13
October 11, 2011 at 17:19:27
I've look at your spreadsheet.

Why do days 1, 2 and 9 show all zeros?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#14
October 12, 2011 at 07:27:33
It is possible for them to be all zeros on some days. Rows 3-5 are the daily number of builds, minor issues and major issues. Some days there are no builds or issues.

Report •

#15
October 12, 2011 at 08:47:57
Here's the tricky part, and only you can decide how to deal with it.

Your charts shows the last cumulative percentage through the end of the month because you have the SUM formulas in Rows 6 - 8 for the entire month, which in turn calculates a percentage for every day based on the latest data available.

The first thing that came to mind was to suggest an IF formula that would not calculate a percentage for days beyond the current day with data, since any days beyond the current would have 0's in Rows 3, 4, and 5.

=IF(AND(F3=0, F4=0, F5=0), 0 ,IF(ISERROR(etc...)

This would force a 0 into Row 9 which would eliminate the percentages that get carried forward for days beyond the current day.

However, if the most recent day (or days) happen to contain 0's in Rows 3, 4 and 5, then you would get a 0 percentage, when I think you really do want the latest percentage, since it's a valid number up until that day.

One way around that might be to add a Row where you could use an X or some other character to tell the formula that even if there are zeros in Rows 3, 4 and 5 on that day, still calculate a percentage.

In other words, if you have numbers that give 65% up to Day 8 but 0's on Days 9 & 10, I assume you want 65% charted through Day 10, but nothing after that. With a Row that contained a "key", such as an X, your formula might be something like:

=IF(F(your key row) = "X", IF(ISERROR(etc...)

The user would simply need to put an X in the column for the current day in order to have the percentage calculated.

Does that make sense?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#16
October 12, 2011 at 09:36:38
Yes, that makes sense. So if I insert a row above the percentage and make that my X row which pushes the percentage down to row 10, what does the fomula look like? I keep trying to do it, but it gives me errors. Thanks

Report •

#17
October 12, 2011 at 10:07:56
✔ Best Answer
When using Nested IF's, you just need to make sure you keep your value_if_true and value_if_false arguments straight.

I can't access your spreadsheet from work, so I can't test this with your data, but it works fine in a new spreadsheet.

Basically, your orginal formula represents the value_if_true for IF(F9="X",...

If that IF is true, then it evaluates your percentage. If it's not (i.e. there is no X in F9) it returns the value_if_false which is 0.

=IF(F9 = "X", IF(ISERROR(1-(F8+F7)/F6),0, (1-(F8+F7)/F6)), 0)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#18
October 12, 2011 at 10:13:56
Great. And is there a way to not plot the zeros at the end?

Report •

#19
October 12, 2011 at 10:44:43
You could try this formula instead:

=IF(F9 = "X", IF(ISERROR(1-(F8+F7)/F6),0, (1-(F8+F7)/F6)), NA())

This will put #N/A in the cells instead of 0 and the points will not be plotted.

If you don't want to see the #N/A in the cells, use Conditional Formatting with this formula and choose a font color of White - or whatever the background color of your percentage cells are:

=ISNA(F10)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#20
October 12, 2011 at 11:28:14
Perfect. Thank you so much for your time and help. I really appreciate it!

Report •


Ask Question