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?
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
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!
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.
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!
How would they do that, exactly?They would manually choose the position and contract, and enter the quantity, premium and strike values
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.
How would they do that, exactly?They would manually choose the position and contract, and enter the quantity, premium and strike values
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.
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.
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
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.
Originally Posted by shg
Thanks! That's pretty much exactly what I wanted! Now my only issue is adjusting the chart series 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/
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks