Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ArrayFunctions: How do I... ?
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+D2:D10+E2:E10+F2:F10+G2:G10+H2:H10 } So my question; *is* there an easier way, a shorter formula? Brian Herbert Withun |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ArrayFunctions: How do I... ?
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" wrote in message oups.com... 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+D2:D10+E2:E10+F2:F10+G2:G10+H2:H10 } So my question; *is* there an easier way, a shorter formula? Brian Herbert Withun |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ArrayFunctions: How do I... ?
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" wrote in ooglegroups.com... 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+D2:D10+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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ArrayFunctions: How do I... ?
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" wrote in message oups.com... 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" wrote in ooglegroups.com... 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+D2:D10+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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Has anyone used arrayfunctions from Alan Beban? | Excel Worksheet Functions |