ArrayFunctions: How do I... ?

[Brian Herbert Withun]

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

 

[Bernie Deitrick]

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

 

[Daniel Gash-Jones]

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

 

[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

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

 

[Bernie Deitrick]

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