Dynamic formula to sum across rows

Hello, Experts!
I'm looking for a (simple?) dynamic formula, probably a SUM
OFFSET-based expression, to enter one time that'll sum across 3
columns, on a single row, into a fourth column. I'd like to be able to
use this formula in the "Refers to" space at the bottom of the "Define
name" dialog box, so that I could further use the said formula with
other named ranges.
EXAMPLE: Simple 4-column table, with numeric values in cols A,B & C.
I'd like the formula to sum across any row, for example row 5, and
deliver the simple sum into D5. I realize I could just select cell D5,
and enter "=SUM(OFFSET($A$5,0,0,1,3)), and it'd give me that one
correct answer. But I'd like to just enter this DYNAMIC formula one
time, and have it take care of results for as far down as there are
values in cols A-C. [Also: I'd prefer not to just enter a simple SUM
formula one time in D2 and then copy it down...that's going to give me
a bunch of trashy zeroes in cells with no source data, plus I wouldn't
be able to use it with a named range, maybe to paste it in at other
locations.]
Thanks for your time & attention to detail ((-;
turtleman2


---
Message posted from http://www.ExcelForum.com/

Turtleman2,

If the columns are always A-C why do you need a dynamic formula? If you
select D5 and then create a named range, say called SUM3 with a RefersTo
value of =SUM(A5:C5), use that in D6, D7, and any others and it will work
okay. The only place it won't work is in columns A-C, but you don't want
that anyway.

Your formula is using offset, but it is not more dynamic than =SUM(A5:C5) as
it has a static number of rows and columns.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"turtleman2 " wrote in message
...
Hello, Experts!
I'm looking for a (simple?) dynamic formula, probably a SUM
OFFSET-based expression, to enter one time that'll sum across 3
columns, on a single row, into a fourth column. I'd like to be able to
use this formula in the "Refers to" space at the bottom of the "Define
name" dialog box, so that I could further use the said formula with
other named ranges.
EXAMPLE: Simple 4-column table, with numeric values in cols A,B & C.
I'd like the formula to sum across any row, for example row 5, and
deliver the simple sum into D5. I realize I could just select cell D5,
and enter "=SUM(OFFSET($A$5,0,0,1,3)), and it'd give me that one
correct answer. But I'd like to just enter this DYNAMIC formula one
time, and have it take care of results for as far down as there are
values in cols A-C. [Also: I'd prefer not to just enter a simple SUM
formula one time in D2 and then copy it down...that's going to give me
a bunch of trashy zeroes in cells with no source data, plus I wouldn't
be able to use it with a named range, maybe to paste it in at other
locations.]
Thanks for your time & attention to detail ((-;
turtleman2


---
Message posted from http://www.ExcelForum.com/

Turtleman2,

OK, how about this then

=SUM(A5:OFFSET(D5,,-1))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"turtleman2 " wrote in message
...
To Bob,
Thanks for the input. The reason I'm looking for a DYNAMIC formula is
that, I don't want to be entering the formula over & over ad infinitum,
every time I enter more data into cols A-C. I'd much prefer to enter
the formula one time, and have the result appear in col D every time I
input more numbers into cols A-C---automatically! I apologize if I'm
not making this clear. Maybe it's something Excel has a problem
with...
At any rate--thanks for your time ((-;|
turtleman2.


---
Message posted from http://www.ExcelForum.com/

To Bob,
Thanks! Your suggestion worked; it's neat & quick. ((-;
I'm assuming the correct way to use it is to enter it, say, in D5,
then copy it up & down as far as necessary, and choose
ToolsOptionsView, and unselect Zero values, to avoid all the
superfluous zeroes in col D. Correct?
Obviously, Bob, you understand 10k% more than I about Excel formulas,
so could you please answer one question?
Your expression uses the OFFSET function; my confusion stems from the
fact that you' (a)only using 3 (instead of the 'required' 4?)
arguments after the function-name, and (b) you're using a negative
number as the last argument. I thought that was verboten. How does your
use of OFFSET get around those issues?
Again, thanks much for your time & attention to detail.
turtleman2.


---
Message posted from http://www.ExcelForum.com/

OFFSET takes 5 arguments, not 4.

Alan Beban

turtleman2 wrote:
To Bob,
Thanks! Your suggestion worked; it's neat & quick. ((-;
I'm assuming the correct way to use it is to enter it, say, in D5,
then copy it up & down as far as necessary, and choose
ToolsOptionsView, and unselect Zero values, to avoid all the
superfluous zeroes in col D. Correct?
Obviously, Bob, you understand 10k% more than I about Excel formulas,
so could you please answer one question?
Your expression uses the OFFSET function; my confusion stems from the
fact that you' (a)only using 3 (instead of the 'required' 4?)
arguments after the function-name, and (b) you're using a negative
number as the last argument. I thought that was verboten. How does your
use of OFFSET get around those issues?
Again, thanks much for your time & attention to detail.
turtleman2.


---
Message posted from http://www.ExcelForum.com/

Hi Turtleman2,

Success, that's good.

Rather than hide the zeros values, you can suppress them, like this
=IF(SUM(A5:OFFSET(D5,,-1))=0,"",SUM(A5:OFFSET(D5,,-1)))
which simply says if the sum equates to 0, fill the cell with blank, else
fill it with the sum.

The arguments to offset are in order,
reference - the cell to offset from
rows - the number of rows to offset
columns - the number of columns to offset
height - the number of rows to be returned
width - number of columns to be returned

As Help says, If height or width is omitted, it is assumed to be the same
height or width as reference. In our example this is 1 column, 1 row. 3 is
the required number of arguments not 4 (although oddly this is valid
=OFFSET(A1,,)).

Negative numbers are okay, it just means that for columns you are counting
left not right, and for rows you are counting up not down. The only thing to
be careful about is that the number of columns or rows that you negatively
offset do not take you off of the sides of the spreadsheet (that actually
applies equally to positively offset, although less likely to happen).

So you see, my use of OFFSET is not getting around these issue, as they are
not issues. It is very standard use of OFFSET.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"turtleman2 " wrote in message
...
To Bob,
Thanks! Your suggestion worked; it's neat & quick. ((-;
I'm assuming the correct way to use it is to enter it, say, in D5,
then copy it up & down as far as necessary, and choose
ToolsOptionsView, and unselect Zero values, to avoid all the
superfluous zeroes in col D. Correct?
Obviously, Bob, you understand 10k% more than I about Excel formulas,
so could you please answer one question?
Your expression uses the OFFSET function; my confusion stems from the
fact that you' (a)only using 3 (instead of the 'required' 4?)
arguments after the function-name, and (b) you're using a negative
number as the last argument. I thought that was verboten. How does your
use of OFFSET get around those issues?
Again, thanks much for your time & attention to detail.
turtleman2.


---
Message posted from http://www.ExcelForum.com/