Dynamic formula to sum across rows

  • Thread starter Thread starter turtleman2
  • Start date Start date
T

turtleman2

Hello, Experts!
I'm looking for a (simple?) dynamic formula, probably a SU
OFFSET-based expression, to enter one time that'll sum across
columns, on a single row, into a fourth column. I'd like to be able t
use this formula in the "Refers to" space at the bottom of the "Defin
name" dialog box, so that I could further use the said formula wit
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, an
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 on
correct answer. But I'd like to just enter this DYNAMIC formula on
time, and have it take care of results for as far down as there ar
values in cols A-C. [Also: I'd prefer not to just enter a simple SU
formula one time in D2 and then copy it down...that's going to give m
a bunch of trashy zeroes in cells with no source data, plus I wouldn'
be able to use it with a named range, maybe to paste it in at othe
locations.]
Thanks for your time & attention to detail ((-;
turtleman
 
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)
 
To Bob,
Thanks for the input. The reason I'm looking for a DYNAMIC formula i
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 ente
the formula one time, and have the result appear in col D every time
input more numbers into cols A-C---automatically! I apologize if I'
not making this clear. Maybe it's something Excel has a proble
with...
At any rate--thanks for your time ((-;|
turtleman2
 
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)
 
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 choos
Tools>Options>View, and unselect Zero values, to avoid all th
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 th
fact that you're: (a)only using 3 (instead of the 'required' 4?
arguments after the function-name, and (b) you're using a negativ
number as the last argument. I thought that was verboten. How does you
use of OFFSET get around those issues?
Again, thanks much for your time & attention to detail.
turtleman2
 
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)
 
Back
Top