hide irrelevant data

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

I plot a variable against the date to make a (scatter) plot. To smoothen the
data, I use the trimmean function. I have copied the formula to the rest of
the column and as a consequence the graph drops down toward zero for the
date after today. This is very ugly. How can I automatically hide the part
that is not relevant? Of course the problem is more general and not
specific for the trimmean function.

Thanks for your help.

Jim
 
Hi Jim,

If I understand what you are trying to do.
You can use Tushar Mehta's Auto Chart Manager to limit
the X and Y axes to values that you specify in the worksheet.

Go to this site and click on the Auto Chart Manager Link.
http://www.tushar-mehta.com/excel/

HTH
Martin
 
If you put errors in those cells beyond today, these will not be
charted:

=IF(your_date>TODAY(),NA(),your_existing_formula)

Alternatively, you could apply a custom filter on the date column and
select Less than or equal to today's date, so that the other rows are
hidden (and do not show on the chart). This is not automatic, though,
so you would have to re-apply the filter each day.

Hope this helps.

Pete
 
If you put errors in those cells beyond today, these will not be
charted:

=IF(your_date>TODAY(),NA(),your_existing_formula)

Thanks, but:
This gives me a ' #N/A ' read out, but the graph is still going down. What
am I doing doing wrong?
 
I don't know - can you describe what you have and what is happening in
more detail?

Pete
 
In colun A I have Friday, June 20, 2008
Saturday, June 21, 2008
Sunday, June 22, 2008
Monday, June 23, 2008
Tuesday, June 24, 2008
Wednesday, June 25, 2008
Thursday, June 26, 2008
Friday, June 27, 2008
Saturday, June 28, 2008
Sunday, June 29, 2008
Monday, June 30, 2008


In column B:
87.7
86.1
86.5
85.6
86.6
85.9
86.7
87.2
87.6
84.7


In column C from the fifth row:

=(TRIMMEAN(B1:B5,0.4))
I have copied this down the column.
The chart plots both column B and C.
Now I have tried to follow your suggestion by putting =IF(A24>TODAY(),NA(),
TRIMMEAN(B20:B24,0.4)) in the cell in row C that corresponds to tomorrow's
date.
This does not seem to work. (gocves the #N/A error).

Once more, thanks for your patience.
Jim



I don't know - can you describe what you have and what is happening in
more detail?

Pete
 
I have tried to mimic your setup. After the first date in A1, I put
the formula:

=A1+1

in A2 and copied down to A20 to give me dates up to Wednesday 9th
July. I put your data in column B, and in C5 I put this formula:

=IF(A5>TODAY(),NA(),(TRIMMEAN(B1:B5,0.4)))

and copied this down - sure enough, from 30th June onwards you get #N/
A showing. I then graphed C5:C20 against A5:A20 as a scatter - this
showed a range of 86.1 to 86.9 on the vertical axis and all the dates
on the horizontal axis, such that all the points were in the first
third (or so) of the graph. However, they were all evenly spread
vertically, which is what I thought you wanted (rather than the axis
plunging to zero with no data).

So, are you now saying that you don't like to see #N/A in column C?

You can overcome this by highlighting all the cells from C5 downwards
and clicking on Format | Conditional Formatting, and then choosing
Formula Is rather than Cell Value Is in the first box and entering the
formula:

=ISERROR(C5)

Then click on the Format button, click on Colour (i.e. foreground
colour), and then select white. Click OK twice to exit the dialogue
box. The values are still there, but when you click elsewhere in the
sheet they will seem to have disappeared giving a blank cell, as you
are now using white foreground on a white background for those cells
which contain an error (a deliberate one in this case).

Hope this helps.

Pete
 
I forgot to mention that I am in Excel 2007. Does that change anything to
your suggestion?

Also, my point is just the chart not what I see in the cells, I just don't
want that the graph makes a spectacular drop down that does not mean
anything.




I have tried to mimic your setup. After the first date in A1, I put
the formula:

=A1+1

in A2 and copied down to A20 to give me dates up to Wednesday 9th
July. I put your data in column B, and in C5 I put this formula:

=IF(A5>TODAY(),NA(),(TRIMMEAN(B1:B5,0.4)))

and copied this down - sure enough, from 30th June onwards you get #N/
A showing. I then graphed C5:C20 against A5:A20 as a scatter - this
showed a range of 86.1 to 86.9 on the vertical axis and all the dates
on the horizontal axis, such that all the points were in the first
third (or so) of the graph. However, they were all evenly spread
vertically, which is what I thought you wanted (rather than the axis
plunging to zero with no data).

So, are you now saying that you don't like to see #N/A in column C?

You can overcome this by highlighting all the cells from C5 downwards
and clicking on Format | Conditional Formatting, and then choosing
Formula Is rather than Cell Value Is in the first box and entering the
formula:

=ISERROR(C5)

Then click on the Format button, click on Colour (i.e. foreground
colour), and then select white. Click OK twice to exit the dialogue
box. The values are still there, but when you click elsewhere in the
sheet they will seem to have disappeared giving a blank cell, as you
are now using white foreground on a white background for those cells
which contain an error (a deliberate one in this case).

Hope this helps.

Pete
 
Hi Jim,

I don't have XL2007, but I understand that it has a different way of
accessing the drop-down menus that we have on older versions - you
might have to click on the Office button to get at the Conditional
Formatting controls.

In my mimic of your set up the graph does not drop off to zero - the
range on the Y axis is from 86.1 to 86.9. The X axis does show all the
dates in my range (from row 5 to 20), but if I change the formula in
A2 to:

=IF(A1+1<=TODAY(),A1+1,NA())

and copy this down, then the dates beyond today will show #N/A (and
can be disguised as described before), but more importantly the graph
then auto-scales to omit the offending dates, so that the points are
evenly distributed in the plot area.

Hope this helps.

Pete
 
OK, only now I see what you meant in your previous post: the auto-scaling.
Yes, that should do it.

Thank you very much for spending all this time!
Jim

Hi Jim,

I don't have XL2007, but I understand that it has a different way of
accessing the drop-down menus that we have on older versions - you
might have to click on the Office button to get at the Conditional
Formatting controls.

In my mimic of your set up the graph does not drop off to zero - the
range on the Y axis is from 86.1 to 86.9. The X axis does show all the
dates in my range (from row 5 to 20), but if I change the formula in
A2 to:

=IF(A1+1<=TODAY(),A1+1,NA())

and copy this down, then the dates beyond today will show #N/A (and
can be disguised as described before), but more importantly the graph
then auto-scales to omit the offending dates, so that the points are
evenly distributed in the plot area.

Hope this helps.

Pete
 
Back
Top