Charting incraments with start/end data points

JMJimmy

New Member
Joined
Oct 3, 2006
Messages
15
Hi All,

I am a serious noob to excel and am just wondering if it's possible to either
a) create a data set using start and end values and applying an incrament.
b) create a chart doing the same.

I'm not familiar with the proper terminology so I'll give an example

The end result I am trying for is a chart with
X axis = 0 to A in incraments of 1 where A is a static, changeable number
Y axis = 0 to B in incraments of 10,000 where B is ((A * 250)+1000)
1st chart line = 1,000 to B in incraments of 250
2nd chart line = 1,000 to B in incraments of 150
Then, if possible, highlight points where B+2400 is a multiple of A.

I'd appreciate any help or advice - note this is not for school or work, it is personal interest only. I am able to create this using PHP/GD but it's time consuming and I'd like to figure out how to do it within Excel but I can't seem to find any options/info to do what is required. I suspect it's something to do with XY scatter charts and trend lines.

Is this possible?

Thanks!

JMJimmy
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
hi - welcome to the board!

you'll need to do this in the data, then chart it.

to get you started - you can set the chart axis range / steps manually (right click axis | format axis | scale). you need vba to automate this / link the scales to cell values. addin to do this here:

http://www.tushar-mehta.com/excel/software/autochart/index.html

once you've got the tools, just need to set up the chart values. eg with 1000 in a1, and B's value in b1, formulas like:

=MIN($A$1+((ROW()-1)*250),$B$1)

=IF(($A$1+((ROW()-1)*250))<=$B$1,($A$1+((ROW()-1)*250)),NA())

..would give you the series. second formula has benefit of returning na() for values > B & na()s aren't plotted.

have a look here:

for a variety of techniques for conditionally highlighting chart data points:

http://peltiertech.com/Excel/Charts/

...post back if you can't get it sorted
 

JMJimmy

New Member
Joined
Oct 3, 2006
Messages
15
Hi PaddyD,

Thank you for the welcome!

I followed most of your reply and got VBA installed. What I didn't get was this:

"you'll need to do this in the data, then chart it. "

Do you mean I'll have to write out the entire data set (which theoretically if a max value isn't put in is exponential) or am I missing something obvious? :)

Currently I'm trying to work with this data set:
_| A | B
1| 0 | 100
2| 1,000 | =SUM(250*B1+A2)
3| 1,000 | =SUM(150*B1+A3)

or

_| A | B
1| 0 | 100
2| 1,000 | 26000
3| 1,000 | 16000

I did an XY scatter Series in rows. Then did format axis->scale: y axis to 1000, x axis to 10 (1 was too many). Everything works for the first and last values, the chart looks right, but I can't figure out how to introduce the incraments without manually typing out all the data (not going to happen! hehe)

Thanks for your reply.

JMJimmy

side note - I really want to do something like the following
foreach($B$1,COUNTER(2)){
(COL()COUNTER())=250*COUNTER()+1000
}
foreach($A$1,COUNTER(2)){
(COL()COUNTER())=150*COUNTER()+1000
}
That would be the easiest way to create the data set then manipulating it is easy from what I've been able to see... but I don't think Excel has a loop function
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
" but I can't figure out how to introduce the incraments without manually typing out all the data (not going to happen! hehe) "

In general, charts need something to chart. The usual way is to chart data from a sheet.

It is possible to chart programmatically - e.g. if you have an equation for the line:

http://www.tushar-mehta.com/excel/software/plot_manager/index.html

...if you wnat to do the vba yourself, then I'll have to back out as vba's not really my thing.
 

Forum statistics

Threads
1,141,665
Messages
5,707,699
Members
421,524
Latest member
Bharath99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top