Hi all:
The attached document has a graph that joins all four points of a data marker properly, till the 10th iteration is encounter. At this point, two data markers do not connect. Any suggestions? Thanks.
- Ravi
Last edited by ravichander; 02-12-2009 at 12:09 AM.
Because the formula in this 2 cells is incorrect.
BoxesScreen7 $U$6 and $S$6
Hi Andy:
Thank you for your response. Yes, that fixed it. However, why are Series Point 13 and 14 in bold? Thanks.
- Ravi
When you say bold do you mean the lines are slightly thicker?
If so this is just a result of the way the chart is drawn. Try changing the size of the chart and you will see the thicker lines may well change to other series.
To avoid this you could just plot a single line rather than 2 between each set of points. More difficult to determine which values to use but the thicker lines will go.
Hi Andy:
Yes, I meant that some lines were thicker. Would an If statement to check whether a line has already been plotted be the way to resolve this? Thanks.
- Ravi
Yes. Only output data for 1 vertical. You just need to determine what values to use. You may still need 2 sets of values but try and avoid laying the lines over each other.
You may be better off using Error bars.
Hi Andy:
Change the Chart Type to Error bars? Thanks.
- Ravi
Not change the chart type. xy-scatter is what you need.
Instead of joining the points with the normal line use x and y error bars with custom values.
A similar approach is used to create a step chart
http://www.andypope.info/charts/stepchart.htm
Hi Andy: Thank you for the link on the XY Scatter with Error Bars. I honestly do not know how to approach this. Further the points have to be joined to form a box and not just steps as in your illustration. Can you get me started on this? Thanks.
- Ravi
You take the principle of the step chart and extend it to make boxes.
See attached.
Andy:
Thank you. I will work on it. Appreciate the help.
- Ravi
Hi Andy:
Here is what I thought of doing.
The following VBA code checks to see if the first cell has any data. If there is data, then it will call a macro that will process the first line of the example that you gave me.
This code processes the first line of your example.Code:Sub DeterminNumberOfBoxesScreen7() Sheets("Pay Grades").Select Range("D20").Select If ActiveCell.Value <> "" Then Call PayGradeBoxesScreen7 Else Sheets("Pay Grades").Select Range("F1").Select End If End Sub
How would I write code to select D21 and check for the existence of any data, and then if there is, increment macro PayGradeBoxesScreen7 to process the next line?Code:Sub PayGradeBoxesScreen7() ' ' PayGradeBoxesScreen7 Macro ' ' ActiveWindow.ScrollRow = 1 Range("F1").Select Sheets("BoxesScreen7").Select Range("G20").Select ActiveCell.FormulaR1C1 = "='Pay Grades'!R[-3]C[-4]" Range("G21").Select ActiveCell.FormulaR1C1 = "=" Range("G21").Select ActiveCell.FormulaR1C1 = "='Pay Grades'!R[-1]C[-5]" Range("H21").Select ActiveCell.FormulaR1C1 = "='Pay Grades'!R[-1]C[-2]" Range("I21").Select ActiveCell.FormulaR1C1 = "='Pay Grades'!R[-1]C[-2]" Range("J21").Select ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]" Range("K21").Select ActiveCell.FormulaR1C1 = "=BoxesScreen7!R[-1]C[-4]-BoxesScreen7!RC[-4]" Sheets("Pay Grades").Select Range("F1").Select End Sub
Thanks.
- Ravi
A minor correction. The Range to be slected in the first part of the code is B20 and not D20. Thanks.
- Ravi
What exactly are you trying to do?
Hi Andy:
I am trying to automate the process of populating data just like you had in your example to then generate the graph. Thanks.
- Ravi
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks