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
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,038
Messages
5,545,672
Members
410,697
Latest member
srishtijain0708
Top