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



KC ppt Qns wrote:
I got it all.

Thanks, Mike, Stefi and Charles.

Have a good day!

Cheers,

On Jul 21, 6:26 pm, "Charles Williams"
wrote:
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 Sitehttp://www.decisionmodels.com

"KC ppt Qns" wrote in ...
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,

On Jul 21, 5:36 pm, Stefi wrote:



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:
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
"KC Excel Qns" wrote:
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,- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -