UPDATING WEEKLY GRAPHS

[tylermdsm]

I have a have a column with data in it...cell Z4 down to cell Z40. At the
end of each week, a new value is added in the column with a formula. I want
to have the graph not include the data when there is no value, instead of
putting the line at zero. Any thoughts?

 

[Pete_UK]

Try something like this:

=IF(your_formula="",NA(),your_formula)

This will display #N/A instead of a blank, but this error will be
ignored by your graph. You can "hide" the #N/A by using conditional
formatting - formula is =ISNA(cell), then choose a white foreground.
(cell is the cell reference).

Hope this helps.

Pete

 

[tylermdsm]

When I do that it takes care of the problem with my graph, thank you very
much. Now I'm running into another problem. I have another cell that is
averageing a few cells, and when their is an #NA in the cell, it causes the
average to also turn to an #NA, is there any way to fix this?

 

[Pete_UK]

You can use this array* formula instead of what you have at present:

=AVERAGE(IF(ISNA(A1:A10),FALSE,A1:A10))

* As this is an array formula then once you have typed it in (or
subsequently edit it) you must use CTRL-SHIFT-ENTER to commit it
instead of the normal ENTER. If you do this correctly then Excel will
wrap curly braces { } around the formula when viewed in the formula
bar - you must not type these yourself.

Obviously, change the two references A1:A10 to suit your range.

Hope this helps.

Pete

 

[Jon Peltier]

Have the formula return NA() when there is not value:

IF(LEN(K40)=0,NA(),K40)

This gives you #N/A in the sheet, but isn't plotted as a point in the line
chart.

- Jon

 

[Jon Peltier]

What I usually do, to keep the formulas simpler, is to use two or more
ranges, one optimized for charting, another for subsequent calculations, and
others for whatever I need them for. perhaps a pretty table for a report.
Worksheet space is cheap, time is expensive.

- Jon