# How to plot discrete data on a large scaled axis?

1. ## 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?

2. ## Re: How to plot discrete data on a large scaled axis?

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.

3. ## Re: How to plot discrete data on a large scaled axis?

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.

4. ## Re: How to plot discrete data on a large scaled axis?

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.

5. ## Re: How to plot discrete data on a large scaled axis?

Originally Posted by Andy Pope
Using cells is the simplest method.
You mean what I'm doing is simplest.

Originally Posted by Andy Pope

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.
can you please elaborate more on how to use a named range?

6. ## Re: How to plot discrete data on a large scaled axis?

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

7. ## Re: How to plot discrete data on a large scaled axis?

I got the idea of CHTVARIABLE, but couldn't understand CHTFACTOR !!

What's the idea of using ISTEXT ?

8. ## Re: How to plot discrete data on a large scaled axis?

the ISTEXT would return an array of Trues, which get treated as 1 when multipled by a value.

9. ## Re: How to plot discrete data on a large scaled axis?

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.

10. ## Re: How to plot discrete data on a large scaled axis?

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.

11. ## Re: How to plot discrete data on a large scaled axis?

Thanks for all the help.

Rest all on this part I'll try myself.

Thanks again.

12. ## Re: How to plot discrete data on a large scaled axis?

on a side note, how to mark this thread [SOLVED].

13. ## Re: How to plot discrete data on a large scaled axis?

Originally Posted by Andy Pope
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
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

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1