+ Reply to Thread
Results 1 to 13 of 13

How to plot discrete data on a large scaled axis?

  1. #1
    Registered User
    Join Date
    01-27-2010
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    28

    Exclamation 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.

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,654

    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.
    Cheers
    Andy
    www.andypope.info

  3. #3
    Registered User
    Join Date
    01-27-2010
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    28

    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. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,654

    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. #5
    Registered User
    Join Date
    01-27-2010
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    28

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

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

    Quote Originally Posted by Andy Pope View Post

    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. #6
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,654

    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
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    01-27-2010
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    28

    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. #8
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,654

    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. #9
    Registered User
    Join Date
    01-27-2010
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    28

    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. #10
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,654

    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. #11
    Registered User
    Join Date
    01-27-2010
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    28

    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. #12
    Registered User
    Join Date
    01-27-2010
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    28

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

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

  13. #13
    Registered User
    Join Date
    05-13-2013
    Location
    Sweden
    MS-Off Ver
    Excel 2003
    Posts
    2

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

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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