Dynamic Offsetting, Names, Arrays, Range, Chart Values. NEED HELP

Hi,

I would like to enter values into a Chart from a Named Array(Range).
The Array should not be an actual Range, but a virtual array based on logic
applied to a Range.
In my case picking values in ColumnB based on values in ColumnA

Ideally I woud like to write something like:
Chartvalues:= OFFSET(ColumnA;INT(ColumnA/3);1)

and then make a Chart out of it.

(I actually want to do something more complex: a variable-width bar chart
using an X/Y scatter, w/o using Bullen's method)

My problem is that ONE offset is applied on the whole array, and not
DYNAMICALLY to the different items.

Need Help!


Bengt

Hi
have a look at
http://www.tushar-mehta.com/excel/ne...rts/index.html
http://peltiertech.com/Excel/Charts/Dynamics.html

for some instructions hoy to create dynamic charts


--
Regards
Frank Kabel
Frankfurt, Germany

Bob Lidgard wrote:
Hi,

I would like to enter values into a Chart from a Named Array(Range).
The Array should not be an actual Range, but a virtual array based on
logic applied to a Range.
In my case picking values in ColumnB based on values in ColumnA

Ideally I woud like to write something like:
Chartvalues:= OFFSET(ColumnA;INT(ColumnA/3);1)

and then make a Chart out of it.

(I actually want to do something more complex: a variable-width bar
chart using an X/Y scatter, w/o using Bullen's method)

My problem is that ONE offset is applied on the whole array, and not
DYNAMICALLY to the different items.

Need Help!


Bengt

Hello Frank,
Thanks, but...

I think I want to do something slightly different:
- construct an array by picking specific data items out of another array.
(and not "linearly" with a starting point and a finishing point).

I could obviously construct a new range with VBA, but thought it would be
elegant to do this "within" Excel.

Bengt

"Frank Kabel" wrote in message
...
Hi
have a look at
http://www.tushar-mehta.com/excel/ne...rts/index.html
http://peltiertech.com/Excel/Charts/Dynamics.html

for some instructions hoy to create dynamic charts


--
Regards
Frank Kabel
Frankfurt, Germany

Bob Lidgard wrote:
Hi,

I would like to enter values into a Chart from a Named Array(Range).
The Array should not be an actual Range, but a virtual array based on
logic applied to a Range.
In my case picking values in ColumnB based on values in ColumnA

Ideally I woud like to write something like:
Chartvalues:= OFFSET(ColumnA;INT(ColumnA/3);1)

and then make a Chart out of it.

(I actually want to do something more complex: a variable-width bar
chart using an X/Y scatter, w/o using Bullen's method)

My problem is that ONE offset is applied on the whole array, and not
DYNAMICALLY to the different items.

Need Help!


Bengt

From your description, it seems like the you might want to adapt the
ideas behind the Excel/Tutorials/Select Markers page of my web site.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Hi,

I would like to enter values into a Chart from a Named Array(Range).
The Array should not be an actual Range, but a virtual array based on logic
applied to a Range.
In my case picking values in ColumnB based on values in ColumnA

Ideally I woud like to write something like:
Chartvalues:= OFFSET(ColumnA;INT(ColumnA/3);1)

and then make a Chart out of it.

(I actually want to do something more complex: a variable-width bar chart
using an X/Y scatter, w/o using Bullen's method)

My problem is that ONE offset is applied on the whole array, and not
DYNAMICALLY to the different items.

Need Help!


Bengt

Dear Tushar,

Thanks for your asssistance!

I will try your website solution. It seems to be a very interesting way
forward.

I was a bit surprised when I got "scolded" by Jon. Bullen's technique is
very nice, but doesn't really fit into my exact needs. (I think, at least)

Then, perhaps I have misunderstood, but my issue is that:
- I don't want the columns to be filled in
- I have very many points and thought that as Bullen's technique multiplies
the number of data points, it could overflow. (I have not researched the
limits.)

Any comments on these two points?

Many thanks,
Bengt
"Tushar Mehta" wrote in message
news:MPG.1a9ffc222ae7cddb989700@news-server...
From your description, it seems like the you might want to adapt the
ideas behind the Excel/Tutorials/Select Markers page of my web site.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Hi,

I would like to enter values into a Chart from a Named Array(Range).
The Array should not be an actual Range, but a virtual array based on
logic
applied to a Range.
In my case picking values in ColumnB based on values in ColumnA

Ideally I woud like to write something like:
Chartvalues:= OFFSET(ColumnA;INT(ColumnA/3);1)

and then make a Chart out of it.

(I actually want to do something more complex: a variable-width bar
chart
using an X/Y scatter, w/o using Bullen's method)

My problem is that ONE offset is applied on the whole array, and not
DYNAMICALLY to the different items.

Need Help!


Bengt

Dear Tushar,

Thanks for your asssistance!

I will try your website solution. It seems to be a very interesting way
forward.

I was a bit surprised when I got "scolded" by Jon. Bullen's technique is
very nice, but doesn't really fit into my exact needs. (I think, at least)

Then, perhaps I have misunderstood, but my issue is that:
- I don't want the columns to be filled in
- I have very many points and thought that as Bullen's technique multiplies
the number of data points, it could overflow. (I have not researched the
limits.)

Any comments on these two points?

Many thanks,
Bengt
"Tushar Mehta" wrote in message
news:MPG.1a9ffc222ae7cddb989700@news-server...
From your description, it seems like the you might want to adapt the
ideas behind the Excel/Tutorials/Select Markers page of my web site.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Hi,

I would like to enter values into a Chart from a Named Array(Range).
The Array should not be an actual Range, but a virtual array based on
logic
applied to a Range.
In my case picking values in ColumnB based on values in ColumnA

Ideally I woud like to write something like:
Chartvalues:= OFFSET(ColumnA;INT(ColumnA/3);1)

and then make a Chart out of it.

(I actually want to do something more complex: a variable-width bar
chart
using an X/Y scatter, w/o using Bullen's method)

My problem is that ONE offset is applied on the whole array, and not
DYNAMICALLY to the different items.

Need Help!


Bengt

Hi Bob -

Sorry if my tone the other day was less that cordial. I did not mean to
scold, I was just trying to expand on my point.

In the projects I do with Stephen's technique, I often use a dummy
series to make the outlines around the colored regions. I've used an XY
Scatter series with lines and no markers to draw the outline, and I've
used a Scatter series with no lines or markers, but with error bars, in
a similar approach to Tushar's Step Chart methodology. If you don't want
the columns filled in, then this reduces your complexity greatly. Don't
even plot them, and you won't worry about overflows.

Another thing about the overflows, even in a large chart, you don't need
more than about 250 to show the colored regions without discontinuities
at the interfaces. You can use algorithms to reduce the number of points
you need in a series to just what is needed to show the effect you want.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______


Bob Lidgard wrote:
Dear Tushar,

Thanks for your asssistance!

I will try your website solution. It seems to be a very interesting way
forward.

I was a bit surprised when I got "scolded" by Jon. Bullen's technique is
very nice, but doesn't really fit into my exact needs. (I think, at least)

Then, perhaps I have misunderstood, but my issue is that:
- I don't want the columns to be filled in
- I have very many points and thought that as Bullen's technique multiplies
the number of data points, it could overflow. (I have not researched the
limits.)

Any comments on these two points?

Many thanks,
Bengt
"Tushar Mehta" wrote in message
news:MPG.1a9ffc222ae7cddb989700@news-server...

From your description, it seems like the you might want to adapt the
ideas behind the Excel/Tutorials/Select Markers page of my web site.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...

Hi,

I would like to enter values into a Chart from a Named Array(Range).
The Array should not be an actual Range, but a virtual array based on

logic

applied to a Range.
In my case picking values in ColumnB based on values in ColumnA

Ideally I woud like to write something like:
Chartvalues:= OFFSET(ColumnA;INT(ColumnA/3);1)

and then make a Chart out of it.

(I actually want to do something more complex: a variable-width bar

chart

using an X/Y scatter, w/o using Bullen's method)

My problem is that ONE offset is applied on the whole array, and not
DYNAMICALLY to the different items.

Need Help!


Bengt

Hello Jon,

Thanks. You're reply is very interesting and encouraging.

Specifally this part:
"I often use a dummy series to make the outlines around the colored regions.
I've used an XY Scatter series with lines and no markers to draw the
outline"

This is EXACTLY what I have been struggeling with! (do you have a solution
posted somewhere?)

Typically I would like to construct what I would call a "CumulatedX-Y
Variable Column Chart"
I'm almost there with the following steps:

baseX=Sheet1!$A$1:$A$100
emptyX=OFFSET(Sheet1!$A$1;0;2;ROWS(baseX)*3;1)
newX=N(OFFSET(Sheet1!$A$1;INT((ROW(emptyX)-1)/3);0;ROWS(emptyX);1))
offsetY=IF(MOD(ROW(emptyX);3)=2;100;(1-MOD(ROW(emptyX);3)))

valX=N(newX)
valY=N(OFFSET(Sheet1!$B$1;INT((ROW(emptyX)-1)/3)+offsetY;0;1;ROWS(emptyX)))

In Column A I have must X values cumulated and in Column B the corresponding
(non-cumulated) Z value. (typically Sales vs. Profitability figures)

Again, I'm ALMOST the
Having cumulated figueres in Col A is not the natural way data is stored.
For some silly reason I can't create an array with running totals
(cumulation) based on a non-cumulated X data.
I have tried the follwoing w/o success.

Cumx=N(SUM(OFFSET(Sheet1!$A$1;0;0;ROW(baseX);1)))
newCX=N(INDEX(Cumx;INT((ROW(emptyX)-1)/3)))

BTW, may I ask you a few questions:
1) what does N() actually do
2) in what order are the (items in) arrays handled?

Bengt


"Jon Peltier" wrote in message
...
Hi Bob -

Sorry if my tone the other day was less that cordial. I did not mean to
scold, I was just trying to expand on my point.

In the projects I do with Stephen's technique, I often use a dummy
series to make the outlines around the colored regions. I've used an XY
Scatter series with lines and no markers to draw the outline, and I've
used a Scatter series with no lines or markers, but with error bars, in
a similar approach to Tushar's Step Chart methodology. If you don't want
the columns filled in, then this reduces your complexity greatly. Don't
even plot them, and you won't worry about overflows.

Another thing about the overflows, even in a large chart, you don't need
more than about 250 to show the colored regions without discontinuities
at the interfaces. You can use algorithms to reduce the number of points
you need in a series to just what is needed to show the effect you want.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______


Bob Lidgard wrote:
Dear Tushar,

Thanks for your asssistance!

I will try your website solution. It seems to be a very interesting way
forward.

I was a bit surprised when I got "scolded" by Jon. Bullen's technique is
very nice, but doesn't really fit into my exact needs. (I think, at
least)

Then, perhaps I have misunderstood, but my issue is that:
- I don't want the columns to be filled in
- I have very many points and thought that as Bullen's technique
multiplies
the number of data points, it could overflow. (I have not researched the
limits.)

Any comments on these two points?

Many thanks,
Bengt
"Tushar Mehta" wrote in
message
news:MPG.1a9ffc222ae7cddb989700@news-server...

From your description, it seems like the you might want to adapt the
ideas behind the Excel/Tutorials/Select Markers page of my web site.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...

Hi,

I would like to enter values into a Chart from a Named Array(Range).
The Array should not be an actual Range, but a virtual array based on

logic

applied to a Range.
In my case picking values in ColumnB based on values in ColumnA

Ideally I woud like to write something like:
Chartvalues:= OFFSET(ColumnA;INT(ColumnA/3);1)

and then make a Chart out of it.

(I actually want to do something more complex: a variable-width bar

chart

using an X/Y scatter, w/o using Bullen's method)

My problem is that ONE offset is applied on the whole array, and not
DYNAMICALLY to the different items.

Need Help!


Bengt

New problem:

My solutions works fine, but freaks out after 85 items!

Jon, it would be great to have a look at your dummy series tool.

Bengt
"Bob Lidgard" wrote in message
...
Hello Jon,

Thanks. You're reply is very interesting and encouraging.

Specifally this part:
"I often use a dummy series to make the outlines around the colored
regions.
I've used an XY Scatter series with lines and no markers to draw the
outline"

This is EXACTLY what I have been struggeling with! (do you have a solution
posted somewhere?)

Typically I would like to construct what I would call a "CumulatedX-Y
Variable Column Chart"
I'm almost there with the following steps:

baseX=Sheet1!$A$1:$A$100
emptyX=OFFSET(Sheet1!$A$1;0;2;ROWS(baseX)*3;1)
newX=N(OFFSET(Sheet1!$A$1;INT((ROW(emptyX)-1)/3);0;ROWS(emptyX);1))
offsetY=IF(MOD(ROW(emptyX);3)=2;100;(1-MOD(ROW(emptyX);3)))

valX=N(newX)

valY=N(OFFSET(Sheet1!$B$1;INT((ROW(emptyX)-1)/3)+offsetY;0;1;ROWS(emptyX)))

In Column A I have must X values cumulated and in Column B the
corresponding
(non-cumulated) Z value. (typically Sales vs. Profitability figures)

Again, I'm ALMOST the
Having cumulated figueres in Col A is not the natural way data is stored.
For some silly reason I can't create an array with running totals
(cumulation) based on a non-cumulated X data.
I have tried the follwoing w/o success.

Cumx=N(SUM(OFFSET(Sheet1!$A$1;0;0;ROW(baseX);1)))
newCX=N(INDEX(Cumx;INT((ROW(emptyX)-1)/3)))

BTW, may I ask you a few questions:
1) what does N() actually do
2) in what order are the (items in) arrays handled?

Bengt


"Jon Peltier" wrote in message
...
Hi Bob -

Sorry if my tone the other day was less that cordial. I did not mean to
scold, I was just trying to expand on my point.

In the projects I do with Stephen's technique, I often use a dummy
series to make the outlines around the colored regions. I've used an XY
Scatter series with lines and no markers to draw the outline, and I've
used a Scatter series with no lines or markers, but with error bars, in
a similar approach to Tushar's Step Chart methodology. If you don't want
the columns filled in, then this reduces your complexity greatly. Don't
even plot them, and you won't worry about overflows.

Another thing about the overflows, even in a large chart, you don't need
more than about 250 to show the colored regions without discontinuities
at the interfaces. You can use algorithms to reduce the number of points
you need in a series to just what is needed to show the effect you want.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______


Bob Lidgard wrote:
Dear Tushar,

Thanks for your asssistance!

I will try your website solution. It seems to be a very interesting
way
forward.

I was a bit surprised when I got "scolded" by Jon. Bullen's technique
is
very nice, but doesn't really fit into my exact needs. (I think, at
least)

Then, perhaps I have misunderstood, but my issue is that:
- I don't want the columns to be filled in
- I have very many points and thought that as Bullen's technique
multiplies
the number of data points, it could overflow. (I have not researched
the
limits.)

Any comments on these two points?

Many thanks,
Bengt
"Tushar Mehta" wrote in
message
news:MPG.1a9ffc222ae7cddb989700@news-server...

From your description, it seems like the you might want to adapt the
ideas behind the Excel/Tutorials/Select Markers page of my web site.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...

Hi,

I would like to enter values into a Chart from a Named Array(Range).
The Array should not be an actual Range, but a virtual array based on

logic

applied to a Range.
In my case picking values in ColumnB based on values in ColumnA

Ideally I woud like to write something like:
Chartvalues:= OFFSET(ColumnA;INT(ColumnA/3);1)

and then make a Chart out of it.

(I actually want to do something more complex: a variable-width bar

chart

using an X/Y scatter, w/o using Bullen's method)

My problem is that ONE offset is applied on the whole array, and not
DYNAMICALLY to the different items.

Need Help!


Bengt

Bengt -

I usually use worksheet ranges for my dummy data. If I can't see them, I
have a harder time figuring out where I went awry. When it's working,
then I might try a defined name. Or I drag the formulas further down the
column than I need, then use a defined name to capture the non-N/A part
of it.

A running sum is simple. If the data starts in cell B2, put this in A2
and fill it down:

=SUM(B$2:B2)

I spent five minutes on a defined name to do this, and although the
formula worked in the worksheet and looked like it should in the name,
it didn't. Which is why I stay in the sheet.

From the online help:

"Returns a value converted to a number. It is not generally necessary to
use the N function in a formula, because Microsoft Excel automatically
converts values as necessary. This function is provided for
compatibility with other spreadsheet programs."

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

Bob Lidgard wrote:
New problem:

My solutions works fine, but freaks out after 85 items!

Jon, it would be great to have a look at your dummy series tool.

Bengt
"Bob Lidgard" wrote in message
...

Hello Jon,

Thanks. You're reply is very interesting and encouraging.

Specifally this part:
"I often use a dummy series to make the outlines around the colored

regions.

I've used an XY Scatter series with lines and no markers to draw the
outline"

This is EXACTLY what I have been struggeling with! (do you have a solution
posted somewhere?)

Typically I would like to construct what I would call a "CumulatedX-Y
Variable Column Chart"
I'm almost there with the following steps:

baseX=Sheet1!$A$1:$A$100
emptyX=OFFSET(Sheet1!$A$1;0;2;ROWS(baseX)*3;1)
newX=N(OFFSET(Sheet1!$A$1;INT((ROW(emptyX)-1)/3);0;ROWS(emptyX);1))
offsetY=IF(MOD(ROW(emptyX);3)=2;100;(1-MOD(ROW(emptyX);3)))

valX=N(newX)


valY=N(OFFSET(Sheet1!$B$1;INT((ROW(emptyX)-1)/3)+offsetY;0;1;ROWS(emptyX)))

In Column A I have must X values cumulated and in Column B the

corresponding

(non-cumulated) Z value. (typically Sales vs. Profitability figures)

Again, I'm ALMOST the
Having cumulated figueres in Col A is not the natural way data is stored.
For some silly reason I can't create an array with running totals
(cumulation) based on a non-cumulated X data.
I have tried the follwoing w/o success.

Cumx=N(SUM(OFFSET(Sheet1!$A$1;0;0;ROW(baseX);1)) )
newCX=N(INDEX(Cumx;INT((ROW(emptyX)-1)/3)))

BTW, may I ask you a few questions:
1) what does N() actually do
2) in what order are the (items in) arrays handled?

Bengt


"Jon Peltier" wrote in message
.. .

Hi Bob -

Sorry if my tone the other day was less that cordial. I did not mean to
scold, I was just trying to expand on my point.

In the projects I do with Stephen's technique, I often use a dummy
series to make the outlines around the colored regions. I've used an XY
Scatter series with lines and no markers to draw the outline, and I've
used a Scatter series with no lines or markers, but with error bars, in
a similar approach to Tushar's Step Chart methodology. If you don't want
the columns filled in, then this reduces your complexity greatly. Don't
even plot them, and you won't worry about overflows.

Another thing about the overflows, even in a large chart, you don't need
more than about 250 to show the colored regions without discontinuities
at the interfaces. You can use algorithms to reduce the number of points
you need in a series to just what is needed to show the effect you want.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______


Bob Lidgard wrote:

Dear Tushar,

Thanks for your asssistance!

I will try your website solution. It seems to be a very interesting

way

forward.

I was a bit surprised when I got "scolded" by Jon. Bullen's technique

is

very nice, but doesn't really fit into my exact needs. (I think, at

least)

Then, perhaps I have misunderstood, but my issue is that:
- I don't want the columns to be filled in
- I have very many points and thought that as Bullen's technique

multiplies

the number of data points, it could overflow. (I have not researched

the

limits.)

Any comments on these two points?

Many thanks,
Bengt
"Tushar Mehta" wrote in

message

news:MPG.1a9ffc222ae7cddb989700@news-server...

From your description, it seems like the you might want to adapt the

ideas behind the Excel/Tutorials/Select Markers page of my web site.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...


Hi,

I would like to enter values into a Chart from a Named Array(Range).
The Array should not be an actual Range, but a virtual array based on

logic


applied to a Range.
In my case picking values in ColumnB based on values in ColumnA

Ideally I woud like to write something like:
Chartvalues:= OFFSET(ColumnA;INT(ColumnA/3);1)

and then make a Chart out of it.

(I actually want to do something more complex: a variable-width bar

chart


using an X/Y scatter, w/o using Bullen's method)

My problem is that ONE offset is applied on the whole array, and not
DYNAMICALLY to the different items.

Need Help!


Bengt