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?

✔ Best Answer

When using Nested IF's, you just need to make sure you keep your value_if_trueandvalue_if_falsearguments 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_truefor 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_falsewhich 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.

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.

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

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.

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.

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.

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%

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.

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?

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.

Hmm...is there a way to post the spreadsheet here for you to look at?

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.

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?

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.

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.

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.

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

When using Nested IF's, you just need to make sure you keep your value_if_trueandvalue_if_falsearguments 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_truefor 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_falsewhich 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.

Great. And is there a way to not plot the zeros at the end?

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.

Perfect. Thank you so much for your time and help. I really appreciate it!

Ask Your Question

Weekly Poll

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History