Automatically adding Vertical lines after certain points in line graph

[matthoffman33]

Hi,

I'll get right to the point. For my X series I have 7 consecutive dates. For my Y series I have different values representing a disease index. An example for the Y series would be 5, 12, 23, 27, 35, 42, 58. I need to some how have a vertical line drawn after every 18th index, so at 18, 36, 54, 72, etc. It doesn't necessarily need to be drawn at that exact value, it could be drawn every time that value is reached. Does this make sense??? Any help would be appreciated.

Thanks,

Matt

[Bernard Liengme]

I have labels ("Date" and "Index") in A1 and B1
In A2:A120 I have a date series
In B2:B120 I have index values
In C2:C120 I have =IF(MOD(ROW()-1,18),NA(),B2)
So if the y-value is the 18th, 36th, ... (rows 19, 37...) I see a value,
otherwise I see #N/A which Excel ignores when plotting
Next I formatted the data series on the chart, on the Y-error Bars tab I
specified Custom errors-bars; left positive empty and in =negative box enter
C2:C120
This gives me drop down lines.
hope this helps
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"matthoffman33" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi,
>
> I'll get right to the point. For my X series I have 7 consecutive
> dates. For my Y series I have different values representing a disease
> index. An example for the Y series would be 5, 12, 23, 27, 35, 42, 58.
> I need to some how have a vertical line drawn after every 18th index,
> so at 18, 36, 54, 72, etc. It doesn't necessarily need to be drawn at
> that exact value, it could be drawn every time that value is reached.
> Does this make sense??? Any help would be appreciated.
>
> Thanks,
>
> Matt
>
>
> --
> matthoffman33
> ------------------------------------------------------------------------
> matthoffman33's Profile:
> http://www.excelforum.com/member.php...o&userid=32148
> View this thread: http://www.excelforum.com/showthread...hreadid=519008
>

[matthoffman33]

Sorry, I must not have explained this well enough. I don't want every 18th value to be charted on the graph, what I want is once the index reaches 18, say on March 3, then there should be a vertical line on the graph. If the index reaches 36 on March 5 then there should be another vertical line, and so on.

I've included a sample of a chart to give you a better idea.

Thanks again,

Matt

[Bernard Liengme]

As I wrote the answer, I wondered if that was what was needed.
Change <In C2:C120 I have =IF(MOD(ROW()-1,18),NA(),B2)>
To =IF(MOD(B2,18), NA(), B2) in C2 and copy down the column
And proceed as I indicated
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"matthoffman33" <[email protected]>
wrote in message
news:[email protected]...
>
> Sorry, I must not have explained this well enough. I don't want every
> 18th value to be charted on the graph, what I want is once the index
> reaches 18, say on March 3, then there should be a vertical line on the
> graph. If the index reaches 36 on March 5 then there should be another
> vertical line, and so on.
>
> I've included a sample of a chart to give you a better idea.
>
> Thanks again,
>
> Matt
>
>
> +-------------------------------------------------------------------+
> |Filename: Chart.pdf |
> |Download: http://www.excelforum.com/attachment.php?postid=4417 |
> +-------------------------------------------------------------------+
>
> --
> matthoffman33
> ------------------------------------------------------------------------
> matthoffman33's Profile:
> http://www.excelforum.com/member.php...o&userid=32148
> View this thread: http://www.excelforum.com/showthread...hreadid=519008
>

[matthoffman33]

Hi,

OK, that works just great but only if the index value lands on 18, 36, 54, ets. I also need a line to be drawn if that value is passed. For example, if the index goes from 17 one day up to 19 the next day. I would need a vertical line to be displayed either on the day for the 19 or approximately half way in between the 17 and the 19. Is this something that is just simpler to do manually?

Thanks,

Matt