Re: named array range- having a brain meltdown

From: Frank Kabel (frank.kabel_at_freenet.de)
Date: 03/31/04 

Date: Wed, 31 Mar 2004 16:18:17 +0200

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 work*** still works 

--
Regards
Frank Kabel
Frankfurt, Germany
Keith R wrote:
> Unfortunately, I need to avoid putting any formulas in the real
> work***- 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 work*** 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 work*** 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" <frank.kabel@freenet.de> wrote in message
> news:OFEPmQqFEHA.3080@tk2msftngp13.phx.gbl...
>> 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
>>
>>
>> --
>> Regards
>> Frank Kabel
>> Frankfurt, Germany
>>
>>
>> Keith R wrote:
>>> 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 work*** 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 work***, 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!
Loading