named array range- having a brain meltdown

[Keith R]

Ok, I've used lots of array ranges (named or not) to come up with a single
value, like the sum of calculations across the indicated range.

For the first time, I want to produce an actual /series/ of values from one
named array range. I have an internal customer who has a workbook that I
can't mess with (to much other junk, adding columns to calculate this would
mess other stuff up), but we need a new calculated series to add to a graph,
and I think an array range should be able to do it.

Example of the data: Customer's workbook has A, B, C: I need to calculate D
in a named range and add it to a graph.

A B C D
Actual Budget Variance Cumulative
121 100 21 21
98 100 (2) 19
111 100 11 30
108 110 (2) 28
115 110 5 33
(etc)

the problem is the workbook has Actual, Budget, and variance, but no
cumulative variance- and now I need to help add that to a graph, so I need
to have an array that calculates all the numbers in D without summing them-
{=SUM(Sheet1!C$2:C15)} doesn't work because it sums the entire range,
whereas I want it to return each value in the array.

I figured I needed a range outside the sum, to produce a range, so I tried
{=IF(Sheet1!C2:C15>0,SUM(Sheet1!C$2:C15),"")} but when I put the name in a
range of worksheet cells, they all give me the big summed number, rather
than each cumulative number (and graph represents the same). When I edit it
to just a single range, it gives me the proper values in a range in the
worksheet, but (based on this named range) it only returns one point on a
graph, because it represents a single point range:
{=IF(Sheet1!C2:C2>0,SUM(Sheet1!C$2:C2),"")}

I've messed with this a while, but something just isn't clicking in my
brain- it is probably simple, but it just isn't coming to me. Any help
appreciated!

 

[Frank Kabel]

Hi
if I understood your example correctly try the following formulas:
D1:
=IF(C1<>"",C1,"")

D2:
=ID(C2<>"",D1 + C2,"")
copy this formula down as far as you need it

 

[Keith R]

Unfortunately, I need to avoid putting any formulas in the real worksheet-
they have all sorts of hardcoded formulas, like =$B$17/$G$42 so if I add a
column, I'm breaking all sorts of other things.

So I need to create a named range, within which is an array formula that
will return the same range of values as your example below, without ever
putting them in worksheet cells. The named range will then be added to an
existing graph as a new series. If it matters, I will be able to determine
the size of the array in advance- I actually have three- in one graph it is
52 (52 weeks), in one it is 26 (half a year of weekly values) and in one it
is only 4 (4 quarters). The corresponding data is set up the same way- the
first is in 52 cells, the second in 26, and the third in 4 cells.

I got a little closer than my first attempt with:
MyNamedRange=IF(Sheet1!$C$6:$C$20>0,SUM(Sheet1!$C$6:$C6),20)
when I paste it into worksheet cells it gives me the cumulative across
multiple cells. However, when I add the named range as the source of a new
series, it doesn't show a cumulative increase, all the values on the graph
are the same (appear to be the max value of the array range).

Many thanks for any advice,
Keith R

 

[Frank Kabel]

Hi Keith
not quite sure I understand you but inserting a new column won't break
your formulas. Excel will change them automatically. Don't see that the
other way is possible. So really the easiest was is to add a column
You may try this on a backup copy and see if the worksheet still works

 

[Keith R]

Frank- thank you for the assitance. I'm still trying to do this without
a new column, and have another idea- don't know if this is possible
or not-

If a standard array formula won't give the range of values I need,
then how about a custom formula? For example, something like
the following- it doesn't work yet (syntax problems) but is it possible
to get a function to return a true "range" of values that could be
assigned to a named range, or directly to a graph as a data source?

Function CumulativeArray(rng As Range)
On Error Resume Next

i = rng.Cells.Count
Dim ReturnArray1(i)
Dim ReturnArray2(i)
p = 0

For Each Range In rng ' To rng.Cells.Count
p = p + 1
ReturnArray1(p) = Range.Value
If p>1 then
ReturnArray2(p) = ReturnArray1(p) + ReturnArray2(p - 1)
Else
ReturnArray2(p) = ReturnArray1(p)
End if
Next

CumulativeArray = ReturnArray2()

End Function

 

[Keith R]

Ok, I finally got it working by creating a custom function to return the
array, and putting that in a named range and having the graph series use the
named range. Posting for the archives, in case anyone else ever needs this
solution. Works like a charm.

Graph series2 =test1.xls!TestRange

TestRange =cumulativearray(Sheet1!IV16:IV30)

UDF:

Function CumulativeArray(rng As Range)
On Error Resume Next

Dim ReturnArray1()
Dim ReturnArray2()
Dim rng2 As Range

i = rng.Cells.Count
ReDim ReturnArray1(1 To i)
ReDim ReturnArray2(1 To i)
p = 0

For Each rng2 In rng ' To rng.Cells.Count
p = p + 1
ReturnArray1(p) = rng2.Value
If p > 1 Then
ReturnArray2(p) = ReturnArray1(p) + ReturnArray2(p - 1)
Else
ReturnArray2(p) = ReturnArray1(p)
End If

Next

CumulativeArray = ReturnArray2()

End Function