I want to know how to write a particular array function in Excel 2000.

Given: A table whose columns are days (S,M,T,W,T,F,S) and rows are
weeks (11/4,11/11,11/18) and whose cells are pagehits (integers)

I want to create a new column, WeeklyTotal, and I want that column
(column I) to be calculated using an arrayfunction.

Using a regular formula I can write I2 =SUM(B2:H2),
and I3 =SUM(B3:H3),
and I4 =SUM(B4:H4),
and ...

Using an array formula, what could I write? Assume I have data in
rows 2:10,
This doesn't work: I2:I10 {=SUM(B2:H2:B10:H10)} It gives a result,
but the wrong one.

This DOES work, but I hope there is an easier way:
I2:I10 {=B2:B10+C2:C10+D210+E2:E10+F2:F10+G2:G10+H2:H10}

So my question; *is* there an easier way, a shorter formula?

Brian Herbert Withun

Brian,

Why do you want an array function? A standard SUM functions works well, as you've found, so there
is no need for an array function.

HTH,
Bernie
MS Excel MVP


"Brian Herbert Withun" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I want to know how to write a particular array function in Excel 2000.
>
> Given: A table whose columns are days (S,M,T,W,T,F,S) and rows are
> weeks (11/4,11/11,11/18) and whose cells are pagehits (integers)
>
> I want to create a new column, WeeklyTotal, and I want that column
> (column I) to be calculated using an arrayfunction.
>
> Using a regular formula I can write I2 =SUM(B2:H2),
> and I3 =SUM(B3:H3),
> and I4 =SUM(B4:H4),
> and ...
>
> Using an array formula, what could I write? Assume I have data in
> rows 2:10,
> This doesn't work: I2:I10 {=SUM(B2:H2:B10:H10)} It gives a result,
> but the wrong one.
>
> This DOES work, but I hope there is an easier way:
> I2:I10 {=B2:B10+C2:C10+D210+E2:E10+F2:F10+G2:G10+H2:H10}
>
> So my question; *is* there an easier way, a shorter formula?
>
> Brian Herbert Withun
>

[Daniel Gash-Jones]

Surely it is easier to just put in a regular sum function ie: =sum(B2:H2) and then copy it down.

On Nov 7, 11:08 am, "Bernie Deitrick" <deitbe @ consumer dot org>
wrote:
> Brian,
>
> Why do you want an array function? A standard SUM functions works well, as you've found, so there
> is no need for an array function.
>
> HTH,
> Bernie
> MS Excel MVP
>
> "Brian Herbert Withun" <bhwit...@gmail.com> wrote in messagenews:(E-Mail Removed)...
>
> >I want to know how to write a particular array function in Excel 2000.
>
> > Given: A table whose columns are days (S,M,T,W,T,F,S) and rows are
> > weeks (11/4,11/11,11/18) and whose cells are pagehits (integers)
>
> > I want to create a new column, WeeklyTotal, and I want that column
> > (column I) to be calculated using an arrayfunction.
>
> > Using a regular formula I can write I2 =SUM(B2:H2),
> > and I3 =SUM(B3:H3),
> > and I4 =SUM(B4:H4),
> > and ...
>
> > Using an array formula, what could I write? Assume I have data in
> > rows 2:10,
> > This doesn't work: I2:I10 {=SUM(B2:H2:B10:H10)} It gives a result,
> > but the wrong one.
>
> > This DOES work, but I hope there is an easier way:
> > I2:I10 {=B2:B10+C2:C10+D210+E2:E10+F2:F10+G2:G10+H2:H10}
>
> > So my question; *is* there an easier way, a shorter formula?
>
> > Brian Herbert Withun

My specific implementation is considerably more complicated that the
'hitcount' example I used in my original post. I have hundreds of
identical formula which occasionally change. I would like to use an
array formula both to reduce the number of formulas in the workbook
and to simplify the evolution of the workbook itself. I'd like to
make a single change rather than a hundred individual changes. I can
copy/paste the formula but then there is the possibility that I will
miss a cell or two, or an entire worksheet.

I also have the impression that the workbook will be smaller and
faster by using array functions.

Mostly, though, it is for convenience to me as I modify the formulas
moving forward

Brian Herbert Withun

Brian,

This formula can be array entered. Select cells I2:I10 (for your example) and enter using
Ctrl-Shift-Enter.

=SUM(OFFSET($B$2,ROW()-2,0,1,7))

Though it will not improve speed noticably, or reliability. It will, however, prevent you from
inserting rows within the block of cells where the formula is entered.

HTH,
Bernie
MS Excel MVP


"Brian Herbert Withun" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Nov 7, 11:08 am, "Bernie Deitrick" <deitbe @ consumer dot org>
> wrote:
>> Brian,
>>
>> Why do you want an array function? A standard SUM functions works well, as you've found, so
>> there
>> is no need for an array function.
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>> "Brian Herbert Withun" <bhwit...@gmail.com> wrote in
>> messagenews:(E-Mail Removed)...
>>
>> >I want to know how to write a particular array function in Excel 2000.
>>
>> > Given: A table whose columns are days (S,M,T,W,T,F,S) and rows are
>> > weeks (11/4,11/11,11/18) and whose cells are pagehits (integers)
>>
>> > I want to create a new column, WeeklyTotal, and I want that column
>> > (column I) to be calculated using an arrayfunction.
>>
>> > Using a regular formula I can write I2 =SUM(B2:H2),
>> > and I3 =SUM(B3:H3),
>> > and I4 =SUM(B4:H4),
>> > and ...
>>
>> > Using an array formula, what could I write? Assume I have data in
>> > rows 2:10,
>> > This doesn't work: I2:I10 {=SUM(B2:H2:B10:H10)} It gives a result,
>> > but the wrong one.
>>
>> > This DOES work, but I hope there is an easier way:
>> > I2:I10 {=B2:B10+C2:C10+D210+E2:E10+F2:F10+G2:G10+H2:H10}
>>
>> > So my question; *is* there an easier way, a shorter formula?
>>
>> > Brian Herbert Withun
>
> My specific implementation is considerably more complicated that the
> 'hitcount' example I used in my original post. I have hundreds of
> identical formula which occasionally change. I would like to use an
> array formula both to reduce the number of formulas in the workbook
> and to simplify the evolution of the workbook itself. I'd like to
> make a single change rather than a hundred individual changes. I can
> copy/paste the formula but then there is the possibility that I will
> miss a cell or two, or an entire worksheet.
>
> I also have the impression that the workbook will be smaller and
> faster by using array functions.
>
> Mostly, though, it is for convenience to me as I modify the formulas
> moving forward
>
> Brian Herbert Withun
>