+ Reply to Thread
Results 1 to 13 of 13

Chart with Adjustible Range?

  1. #1
    Registered User
    Join Date
    12-08-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    2010
    Posts
    16

    Chart with Adjustible Range?

    Completely new to macros. I want to make a chart with an adjustible range - where someone could enter min and max values and the size of the increments between them, and have it adjust the graph accordingly. I've heard you can do this with VBA but could someone please give me some hints?

  2. #2
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Chart with Adjustible Range?

    Kia ora,

    this kind of dynamic charting is typically done without VBA, by using dynamic range names. If you upload a sample file that resembles your data structure, I'd be happy to take a look.

    cheers, teylyn

  3. #3
    Registered User
    Join Date
    12-08-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    2010
    Posts
    16

    Re: Chart with Adjustible Range?

    Options Model.xlsx

    The independent variable is Market Rate - cell F2 in Sheet1. I want a user to be able to enter a max value in cell K2, min in K3 and the size of the increments in K4. From this a chart would be produced showing the impact of changes in the market rate on profit/loss based on the range entered.

    Thanks for having a look, let me know if you have any ideas!

  4. #4
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Chart with Adjustible Range?

    A chart needs numbers for the Y axis and categories for the X axis.

    I understand that you want this to be dynamic, but I have no idea what chart you are after.

    You'll need a data table as the source for your chart. You can populate the data table using formulas based on the user input.

    Then you can create a chart from that data table.

    Your sample file has one row of data on one sheet and one row of data on another sheet. But there is no indication of how this might be used in a chart. What data do you want to chart?

    Please do the following: Mock up a chart ( and the underlying data ) manually. Explain how the logic in words of how the user input will generate the values you want to chart.

    Post that file and let's see how we get on.

  5. #5
    Registered User
    Join Date
    12-08-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    2010
    Posts
    16

    Re: Chart with Adjustible Range?

    Options Model with Values.xlsx

    So here's what I want to be able to achieve dynamically. I want to be able to graph how a range of market rates (in this case 0.75 to 0.85, in 0.0001 increments) affect profit. As you can see I had to drag out a thousand cells of values - this is what I want the user to avoid. In the green box I'd like them to just be able to enter a range, and then have a table of values form automatically based on their chosen range. They would manually choose the position and contract, and enter the quantity, premium and strike values (all of these are to be held constant - the only change being graphed is the impact of market value on profit).

    Let me know if there's anything else I need to mention!

  6. #6
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Chart with Adjustible Range?

    They would manually choose the position and contract, and enter the quantity, premium and strike values
    How would they do that, exactly?

    Your sample data has the same values only for these five parameters. If you want to create a chart that is based on a combination of values and other properties, then you may want to explore pivot tables and charts.

    Pivot charts can easily be filtered by several different columns in the source table.

    I have a suspicion that your data sample does not present the whole picture. Any suggestion based on an incomplete data source is bound to be incomplete, so please show the WHOLE scenario where filtering the five parameters is possible.

  7. #7
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Chart with Adjustible Range?

    They would manually choose the position and contract, and enter the quantity, premium and strike values
    How would they do that, exactly?

    Your sample data has the same values only for these five parameters. If you want to create a chart that is based on a combination of values and other properties, then you may want to explore pivot tables and charts.

    Pivot charts can easily be filtered by several different columns in the source table.

    I have a suspicion that your data sample does not present the whole picture. Any suggestion based on an incomplete data source is bound to be incomplete, so please show the WHOLE scenario where filtering the five parameters is possible.

  8. #8
    Registered User
    Join Date
    12-08-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    2010
    Posts
    16

    Re: Chart with Adjustible Range?

    Simply by entering values in the appropriate cells in the spreadsheet?

    I want those parameters to remain constant. They can be changed obviously but for the sake of each chart they need to be held constant. The only factor that is being measured is the impact of market interest rates on profit/loss.

    I don't really know what the whole picture is? I generated that data sample manually, to show what I wanted using the example of 0.75 to 0.85. What I want to happen is for that data to be generated automatically when someone enters the min/max/increment values in the cells I've marked on sheet1.

  9. #9
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    4,794

    Re: Chart with Adjustible Range?

    For a multitude of different type of Charts, have a look at Tushar Mehta's site
    http://www.tushar-mehta.com/excel/ne...rts/index.html

  10. #10
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,589

    Re: Chart with Adjustible Range?

    If I understand what you want, shouldn't it be as simple as creating the desired function in column F? Something like

    F2 =MIN($K$2:$K$3)
    F3 =IF(F2>=MAX($K$2:$K$3),"",F2+$K$4) and copied down column F

    A user should then be able to enter min, max, and step in K2:K4 and the chart should update automatically to reflect the users desired range.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  11. #11
    Registered User
    Join Date
    12-08-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    2010
    Posts
    16

    Re: Chart with Adjustible Range?

    Thanks! That's pretty much exactly what I wanted! Now my only issue is adjusting the chart series range

  12. #12
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,589

    Re: Chart with Adjustible Range?

    How does the chart series range need to be adjusted? Perhaps you are looking for the idea of a dynamic named range: http://peltiertech.com/dynamic-charts/

  13. #13
    Registered User
    Join Date
    12-08-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    2010
    Posts
    16

    Re: Chart with Adjustible Range?

    Well, as the range entered by the user varies, the range of data required for the chart does as well. I did some research and came across dynamic named ranges, though I found it easier to simply extend the formula over many thousands of cells and then place a filter to remove any unnecessary cells. I found a macro online that automatically updates the filter as the data changes. This is a very rough and ready way of doing it though, I will probably change it to the dynamic ranges.

    Thanks for all your help anyway

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 15
    Last Post: 12-22-2014, 08:03 AM
  2. Replies: 0
    Last Post: 12-10-2014, 11:30 AM
  3. [SOLVED] chart based on dynamic range which resizes as per data in range-formula / vba
    By KK1234 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-11-2014, 04:02 PM
  4. [SOLVED] Can I use named range in data range box when creating pie chart?
    By BJackson in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 08-17-2005, 01:05 PM
  5. Using Adjustible String
    By eeboater in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-27-2005, 04:22 PM

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