Charting incraments with start/end data points

[JMJimmy]

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

 

[PaddyD]

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]

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

 

[JMJimmy]

Is it possible to use VB code to create the data set?

JMJ

 

[PaddyD]

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