Chart, mark value that varies

[Carina33]

Hi!

I was hoping someone could help me with this problem.

I have a line chart that is made up of three different columns of data. The data are in three different columns, for ex col A holds the first data series, col B the second series etc. The first data set (a series of positive numbers) and the second data set (a series of negative numbers) make up the third series so that the first entry in series three in col c is the sum of entries in cell A1 and B1 and so on. The numbers is series A and B will vary because it depends on what numbers the user inputs in series A (there is a formula in B that uses the value in A to calculate the value in B). At some point the values in C will become positive (as the user enters more and more values in column A) and I want this point to be displayed, or marked in some way on the chart. Is this possible given that the point will vary depending on the values input in col A by the user?

If anybody could let me know how to do this either using cells or using a macro I would be really grateful.

Thanks much in advance

Carina

[CodeMakr]

You only want the point in which the numbers begin to be positive to show on your chart (versus normal chart)?

[Carina33]

Hello!

I am not sure I was clear enough in my description but you know for example when you create a chart in Excel you can choose to display the values on each of the points in the graph (in addition to having values along the x-axis and y-axis)? Well, I do not want all the values to show I only want the value to show for where the numbers begin to be positive numbers. If the value cannot be shown then some kind of mark i.e a textbox or * could highlight that point on the graph.

Thanks in advance,

C

[JimmyTheHand]

Open the attachment, so that you can understand the explanation.
The two charts are almost identical. There are two columns shown on both.
In case of the 1st chart the second column holds no data. As soon as I move one data point from column#1 to column#2, (see 2nd chart,) it's color changes. Is this a good enough mark for you?

Jimmy

[Carina33]

Hi Jimmy!

The mark is good enough but how I don't understand how I can get this to work in my chart. My chart has three lines(one for each of col A, B and C. Let's say the data you have in col A in your example is the data that my user inputs and the data you have in col B are my negative numbers which have been calculated using the numbers entered by the user in col A. In Col C is the total of col A and B so that the value in C1 is the total of A1 and B1, C2 is total of A2 and B2 and so on. Now, the values in Col A, B and C are all plotted as three different series/lines in my chart. Most of the time the values in Col C will be negative (because of the large negative values in Col B) but the first time it will be a positive number, then I want this point marked in the chart.

Do let me know if it not clear what I mean, I would greately appreciate if if you can find solution.

Thanks so much

C

[CodeMakr]

Not sure if I'm understanding what you are looking for, but you can adjust Y-axis scale (min=0 versus "auto") and negative numbers won't show on your chart.

[CodeMakr]

Like this....or do you need your column B to show its negative numbers and only column C when/if it gets positive?

[Carina33]

Hello!

Thanks a lot for this. In the chart I need all three series to be displayed/shown regardless if the number is positive or negative however, the point where series A and B intersects i.e the first point at which series C takes a positive number should be marked in the chart, in the example this is the value in C26.

Thanks again for your comments and help.

C

[JimmyTheHand]

Try the attached file. See the code module of Sheet1 for details.
The macro reacts to changes on Sheet1, as long as the chage occurs in the range defined as Rng. It's the range where chart data is stored on Sheet1. If you put more data into the chart, you'll have to modify the definition of Rng.

Thanks to CodeMakr for the sample data.

Jimmy

[Carina33]

Thanks Jimmy

It is very helpful.

Best

C

[JonPeltier]

...when you create a chart in Excel you can choose to display the values on each of the points in the graph (in addition to having values along the x-axis and y-axis)? Well, I do not want all the values to show I only want the value to show for where the numbers begin to be positive numbers. If the value cannot be shown then some kind of mark i.e a textbox or * could highlight that point on the graph.

To show all positive values, use a custom number format like "0;;;" or "0.00;;;"
To show just the first positive value, use another column. Column C has the values, so use Column D. First, in E1 enter this helper array formula:
=MIN(IF(C1:C20>0,C1:C20,1E300))
where C1:C20 is the range of values in column C. Don't merely enter this formula, you have to array-enter it, which means hold CTRL+SHIFT while pressing Enter.
Now in D1 (assuming no column headers in the first row) enter this formula:
=IF(C1=$E$1,C1,"")
and fill this down column D as far as you have data in column C.
Now use Rob Bovey's Chart Labeler (free Excel utility at http://appspro.com) to label series C with the labels in column D.