Avoid charting text values generated by formula? 1

[VBAjedi]

In Excel 2003: I have a line chart that uses 12 cells containing formulas as its data source. Each formula checks to see if there's data in a given column, then returns a SUBTOTAL() if so and a string ("No data yet") if not. (The columns the formulas look at contain actual expenses for a given month in the current year, so columns for months that haven't happened yet are empty). An example of the formula:

=IF(COUNTA(A7:A20000)=0, "No data yet", SUBTOTAL(9,A7:A20000))

All I need to figure out is how to keep the line chart from plotting a zero when the formula returns the string. Basically in that case I want the chart to behave as if the cell with the formula in it is completely empty. I'd be willing to change the formula to return something else than that string (an #N/A error value, etc) if it got the graph working correctly...

This has to be stupidly simple! Thanks for your help.

VBAjedi

 

[Gruuuu]

here's 2 options:

Have some VBA that clears the contents of cells that you don't want graphed

or

only produce data points you want graphed

Second option would require a separate data source that only lists dates that have appropriate data. This one can be done with formulas, and does not require fidgeting with the subtotal formulas.

Which do you prefer?

 

[VBAjedi]

It would be easy to do this in VBA but there's no other VBA in this workbook and I was trying to keep it that way...

I'm fine with the second option if it's actually an option for me. The reason I say that is that the chart also has a second series representing budgeted data for the year, and the dates for the x-axis of the chart come from that data.

The point is to chart Budget subtotals versus Actual Expense subtotals, and while the budget line should show the full year, the Actuals line should only appear as far out as we HAVE actuals.

VBAjedi

 

[SkipVought]

I can think of no good reason to have "No data yet" in unused cells to be used for aggregations.

I have charts that have a pre-determined number of unused periods in the future, but is only plot them if there is data. AN EMPTY cell can be never plotted. a ZERO cell is. Check your Chart options.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[VBAjedi]

Right, Skip... I'd be fine with the formulas not returning any text, but the source range for the chart contains formulas that have to return SOMETHING, and I don't know how to write an IF formula that returns "empty" in one case...

VBAjedi

 

[SkipVought]

You need to MANUALLY adjust your formulas to extend ONLY as far as the ACTUAL data...
[tt]
=SUBTOTAL(9,SomeDynamicNamedRange)
[/tt]
or something similar.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gruuuu]

Another alternative, if you have a line graph and don't wish it to be broken: calculate the middle value of a column that has nothing:

1-Dec	2
2-Dec	4
3-Dec	4
4-Dec	5.5  [=B3+(B5-B3)/2]
5-Dec	7

 

[VBAjedi]

I'd never played with Dynamic Named Ranges but that's it, Skip! For the sake of anyone reading this later here's what I did:

My source range (containing the SUBTOTAL formulas) for my chart is C9:C20 on a sheet named "One Page Summary". I set the IF formulas in those cells to return a subtotal of my Actuals data if there was Actuals data for that month, and "" if not.

Then in Insert>Names>Define I defined a dynamic named range called "ActualsToDate" as follows:

=OFFSET('One Page Summary'!$C$9,0,0,(12-COUNTIF('One Page Summary'!$C$9:$C$20,"")),1)

It correctly refers to the block of cells that contain numeric values (it gets off if I have a month with no Actuals data followed by a month WITH actuals data, but our business processes will keep that from happening).

Finally, I set the source for the Actuals series on my chart to ='MyWorkbookName.xls'!ActualsToDate

Works great... have a star for the idea to check out dynamic named ranges!

VBAjedi