locked
Find y values from a data series in MS Excel chart RRS feed

  • Question

  • Greetings,

    I have a MS Excel chart with 3 data series. I would like to write a procedure that will loop through the x axis from values 1 to 100 and for each of the x values it will look up the corresponding y values from the 3 data series in my chart.

    Is there a simple routine anywhere that does this?

    Best,

    ViennaNight

    Monday, July 2, 2018 2:19 PM

All replies

  • Are you do this in Excel or in VB.NET code?

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, July 2, 2018 3:02 PM
  • In VBA! I figured it should be close enough!
    Monday, July 2, 2018 4:49 PM
  • Hello ViennaNight,

    >>I would like to write a procedure that will loop through the x axis from values 1 to 100 

    Is it a further issue from the thread? If so, the value in varDivA should be the y values. I think you could loop through the varDivA to get the y values. Did I misunderstand anything?

    If so, I would suggest you detail your requirement based on the data you previously provided.

    Thanks for understanding,

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, July 3, 2018 6:00 AM
  • Hi Terry,

    It is for the same workbook. 

    I scratched the old code and need to create a new one. varDivA will contain both x and y values for each point; however, I'm not interested in the x and y values captured by varDivA anymore but rather want to loop through my x axis from 1 to 100 with increments of 1 and for each value on the x axis, i.e. 1, 2, 3, 4,, 100 I want to look up its corresponding y value by using the cluster curve in the workbook. In other words, if you are going to do this manually in Excel, you can just point the cursor on the curve for each x value and it will display the y value. This for the same workbook I previously gave you access to. Thanks for you interest.

    ViennaNight 

    Tuesday, July 3, 2018 12:57 PM
  • Hello ViennaNight,

    I note the "Normalized" sheet shared in your workbook. It seems that Period is the Sequence.

    You want to list the Time Elapsed for each Period/Sequence,right?

    If so, since you have the source data of the chart, I think you could try to get the list from the source data directly.

    You even not need VBA code. A simple formula could do this.

    For instance, enter "=IFERROR(VLOOKUP(Normalized!A2,'Div A'!A:F,6,0),"No Data")" in B2 and fill down it to the last row. It will show you the list of Div A for each Period.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, July 4, 2018 8:17 AM
  • Thanks Terry!

    I'm not interested in the data from the data source as is but rather can use the data to interpolate the new values. The new values will be interpolated from new x values using a scale of 1 to 100.

    In other words what I'm trying to accomplish is to normalize the three curves from DivA, DivH and DivP by using one scale for all of them (1 to 100) and determine the Y values for each series for each x value from 1 to 100. I think this will be more of an interpolation exercise in VBA. 

    I'm going to give it a shot next weekend when I have some time and will share the results with you, if I come up with anything. You can do the same of course.

    Best,

    ViennaNight

    Wednesday, July 4, 2018 10:10 PM
  • Excel 2016 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
    Interpolate 3 series to a common scale.
    No VBA.
    http://www.mediafire.com/file/adbaz1evkl1eyn4/07_05_18a.xlsx/file
    http://www.mediafire.com/file/p2rmccieolzcaft/07_05_18a.pdf/file

    Friday, July 6, 2018 3:54 AM
  • Hello ViennaNight,

    >> The new values will be interpolated from new x values using a scale of 1 to 100.

    To be honesty, I'm still wondering what does it mean. Do you want to divide them equally into 100 scales? Could you make some simple data to show us your logic? For instance, what's the value for scale 1 of DivA should be? what's the scale 2 of DivA? the scale 3? etc...

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, July 9, 2018 7:36 AM
  • Not to worry Terry, I will send you some graphical illustration and a detailed description of what I'm trying to get out of this soon. Just give me few days. Thanks for all your interest.

    ViennaNight

    Tuesday, July 10, 2018 12:58 PM