How to plot discrete data on a large scaled axis?
I need to plot some data against time.
Primary axis scale used is 0 to 100, while the secondary is -100 to 0.
Problem is I need to plot some data which is in discrete form (i.e. 0 or 1) on the same chart.
Usually I make an another column with a formula multiplying the discrete data by a no. say 50 and use this column to plot.
Is there any better way of doing this?
Last edited by Parv; 01-30-2010 at 12:46 PM.
Not really.
Basically you are trying to plot data with 3 different Y value ranges but the chart only supports up to 2. Therefore one of the sets of data needs to be refactored.
Thanks for the reply Andy.
I know discrete data needs to be factored. What I asked was if there is another method instead of creating a column with formula?
May be a macro or some trick in chart series formula?
Macro will replace the original data which I want to avoid.
Using cells is the simplest method.
You could use a macro to calculate the values are write a static series formula but this is limited to 1024 characters.
You might be able to use a named range.
You mean what I'm doing is simplest.Originally Posted by Andy Pope
can you please elaborate more on how to use a named range?
Named range, based on category labels for chart, which are in range A2:A5. And a fixed value in F1
CHTFACTOR: =ISTEXT(Sheet1!$A$2:$A$5)*Sheet1!$F$1
Named range, based on values with an added constant of 50
CHTVARIABLE: =Sheet1!$D$2:$D$5*50
I got the idea of CHTVARIABLE, but couldn't understand CHTFACTOR !!
What's the idea of using ISTEXT ?
the ISTEXT would return an array of Trues, which get treated as 1 when multipled by a value.
Ok, so basically ISTEXT(RANGE) would return an array of all one when the range contains all text, or the range can be value also?
How do I use the Named Range for variable size of data, like the one you formed has four data's D2 to D5. What if the size increase up to 10? I don't want to formulate the Named Range again?
And ya Thanks for quick response.
then you can use the dynamic named range techniques, of which there are many on the forum.
Basically you would use a COUNT or COUNTA formula in conjunction with OFFSET.
Of course if you are drawing a straight line you could simple use a xy-scatter with a pair of x,y values.
Thanks for all the help.
Rest all on this part I'll try myself.
Thanks again.
on a side note, how to mark this thread [SOLVED].
Hello Andy,
I have a general question about your codes. When I opened the excel sheet I could not see your code in Macro;however, your CHTFACTOR and CHTVARIABLE could be seen in the formula bar without to see how they are defined. Could you explain how you did it ?
Br,
Sam
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks