Named Array

  • Thread starter Thread starter KC Excel Qns
  • Start date Start date
K

KC Excel Qns

Can someone please help me out.

I would like to use a Name to contain 12 values, either 0s or 1s.

If I want to compute YTD June 2009, this name will contain
1,1,1,1,1,1,0,0,0,0,0,0 and multiplied by a row of 12 months data. I
am thinking of using array.

Likewise, if I want to compute Rest of Year 2009, this name will
contain 0,0,0,0,0,0,1,1,1,1,1,1 multiplied by a row of 12 months
data.

Regards,
 
Hi,

It's not very clear what you actually want to do but consider SUMPRODUCT. A
formula such as the one below may help in which if the month in A1 to A10 is
prior to August (<=7) then column B is summed.

=SUMPRODUCT((MONTH(A1:A10)<=7)*(B1:B10))

Mike
 
Thanks, Mike.

A1 to L1 contains some values with A1 for Jan 09, B1 for Feb 09 etc
etc therefore L1 would be for Dec 09.

We are now in the month of July. I would like to compute YTD Actual
and Rest of Year Forecast. I have to compute YTD and RoY every month.

In the month of July, YTD would be sum of A1:F1 whereas RoY sum
(G1:L1).

I prefer not to use 12 cells to keep track of the 1s and 0s; instead I
would like to use Name to store, say YTD = {1,1,1,1,1,1,0,0,0,0,0,0}
and RoY = {0,0,0,0,0,0,1,1,1,1,1,1} which change every month. And I
only need to change the contents of these 2 Names every month for the
rest of the calculations to work.

I researched exhaustively but cant seem to find a way to define an
array in Name.

Let me know if you need more clarifications. Thanks again.

Rgds,
 
I guess what the OP wants but I can't do it (maybe it cannot be done):
Using ={1;1;1;1;1;1;1;1;1;1;1;1} as source formula of a Name returns an
array of 12 element (constant 1s). He want the element automatically change
value according to a certain month value, e.g. if this value is 6, then the
returned array should be ={1;1;1;1;1;1;0;0;0;0;0;0}

Stefi


„Mike H†ezt írta:
 
Yes getting there. Thanks.

I have defined namedarray = ={1;1;1;1;1;1;0;0;0;0;0;0}

Next I enter 1 1 1 1 1 1 1 1 1 1 1 1 into A1:L1.

The formula {=SUM(namedarray*$A$1:$L$1)} yields 72! Dont understand.
Expecting the result to be 6!!!

Pls help!

Rgds,
 
in Array constants ; (semicolon) separates Rows and , (comma) separates
columns, so at the moment you are multiplying 12 rows by 12 columns which
yields 144 elements half of which are 1 and the other half zero so Sum=72

What you are looking for is
={1,1,1,1,1,1,0,0,0,0,0,0}

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

Yes getting there. Thanks.

I have defined namedarray = ={1;1;1;1;1;1;0;0;0;0;0;0}

Next I enter 1 1 1 1 1 1 1 1 1 1 1 1 into A1:L1.

The formula {=SUM(namedarray*$A$1:$L$1)} yields 72! Dont understand.
Expecting the result to be 6!!!

Pls help!

Rgds,
 
If I want to compute YTD June....

As an alternative, to compute the sum of the first 6 cells in a range...

=SUM(OFFSET(A1:L1,0,0,1,6))

The Last 6 cells...

=SUM(OFFSET(A1:L1,0,11,1,-6))

= = = = = =

HTH :>)
Dana DeLouis
 
Back
Top