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

[Bob Lidgard]

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

 

[Frank Kabel]

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

for some instructions hoy to create dynamic charts

 

[Bob Lidgard]

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

 

[Jon Peltier]

Bob -

You can do all kinds of "elegant" things with named ranges and formulas
in Excel. You can't link the entire source data range of a chart to a
dynamic named range. But it isn't much additional work to define a named
range for each X and Y range in the chart, and these will keep pointing
from the series back to the current definition of the dynamic range.

And if Stephen's variable width column chart isn't an elegant
implementation of formulas and dynamic names, then I have yet to see
one. I use variations of this in many of my charts. (Thanks, Stephen!)

- Jon

 

[Tushar Mehta]

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

 

[Bob Lidgard]

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

 

[Bob Lidgard]

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

 

[Jon Peltier]

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

 

[Bob Lidgard]

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 there:
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

 

[Bob Lidgard]

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

 

[Jon Peltier]

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

 

[Bob Lidgard]

Jon,

I suppose I can live with the cumulated sum problem (Although oddly trivial)

What disturbs me more is the fact that my solution is actually crippled in
size after only 85 pairs of data.
It is clearly a "name computational" problem since the chart can handle over
2000 pairs converted manually (to 6000) w/o any issue.

Do you have any guess around remedies for this?


Bengt

 

[Stephen Bullen]

Hi Bob,

What disturbs me more is the fact that my solution is actually crippled in
size after only 85 pairs of data.
It is clearly a "name computational" problem since the chart can handle over
2000 pairs converted manually (to 6000) w/o any issue.

Do you have any guess around remedies for this?

In the definition for valY:

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

the last parameter is making it one row high and ROWS(emptyX) wide. Even
though we're using defined names, they're still limited to 255 columns. As
you're multiplying your ranges by 3, 255/3 = 85, hence the limit you're
seeing.

Perhaps that should be:

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

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.ie

 

[Bob Lidgard]

Hi Stephen,

Absolutely brilliant!

I don't know where you started, with the 85*3 = 255 that rang a bell or
somewhere else, but your analysis is perfect.
It solved my problem at once!

Since I have the "Dynamic Names Charting Maestro" on line, may I ask two
questions:

a) Is my solution the way you would solve a simpler version of your
variable-width chart?
b) How do I crack making a running total or cumulative values
Shouldn't something like this work for the cumulation array:
Cumx=N(SUM(OFFSET(Sheet1!$A$1;0;0;ROW(baseX);1)))

and to build my enlarged cumulated "newX" array:
newCX=N(INDEX(Cumx;INT((ROW(emptyX)-1)/3)))

Thanks,

Bengt

_____________________________________________________________________
Variabel-width code:
_____________________________________________________________________
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;10000;(1-MOD(ROW(emptyX);3)))

(note that the "10000" offset is a trick to collect a "0" value outside of
the initial data range. Not so elegant, I suppose.)

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

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

_____________________________________________________________________
Cumulation problem:
_____________________________________________________________________
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)))
_____________________________________________________________________

 

[Bob Lidgard]

Hi Stephen,

b) Cumulative values

Not to sure what the KISS principle is (acronym?)

Matrix math is a good idea.
However, it seems like I would need a square Matrix of the length of my
vector.
That would again crack my limit of 256 items!

Bengt
_________________________________________________
"Stephen Bullen" wrote

Hi Bengt,

Since I have the "Dynamic Names Charting Maestro" on line, may I ask two
questions:
a) Is my solution the way you would solve a simpler version of your
variable-width chart?

b) How do I crack making a running total or cumulative values

I don't know <g>. I've never figured out how to do it. I imagine it
would be possible using big 2D forumlas and matrix math, but I've always
defered to the KISS principle at those times.

Regards
Stephen Bullen

 

[Steve L.]

Not to sure what the KISS principle is (acronym?)

Keep It Simple Stupid!