help with formula please

[catnohat]

Hi

What I want to do is add a2 to b1 and have the total show in b2.
I also want it set up so that when a number is entered in the a column
it is automaticaly added to the previous b colum cell and then totaled
across from that a cell.
thank you for your help


+-------------------------------------------------------------------+
|Filename: excel test 1.doc |
|Download: http://www.excelforum.com/attachment.php?postid=4445 |
+-------------------------------------------------------------------+

 

[Guest]

Type the following in cell b2

= sum(a:a) + b1

 

[Biff]

Hi!

Enter this formula in B2 and copy down as needed:

=IF(A2="","",SUM(B$1,A$2:A2))

Biff

 

[catnohat]

I appreciate the effort galami, but when I put a figure in the a colum
it still will not automaticaly add to the previous b column cell and
then show the concurrent total. I hope someone can help me.

Thanks to everyone for trying!!

 

[catnohat]

Thanks, but that formula still isn't quite what I'm looking for. I have
included an attachment of an excel snippet with an explanation, perhaps
this will clearify my question. I want to thank you and everyone for
your help.


+-------------------------------------------------------------------+
|Filename: excel example 2.doc |
|Download: http://www.excelforum.com/attachment.php?postid=4446 |
+-------------------------------------------------------------------+

 

[Biff]

Hi!

My formula does exactly what you're asking for.

When you say:

that formula still isn't quite what I'm looking for.

I may not know what you're looking for but it does exactly what you've asked
for.

Based on your sample 2 doc the result in cell B3 should be 74 and that's
what my formula will give you.

We must be missing something in translation!

Biff

 

[David McRitchie]

that certainly would not be very efficient. In fact it wouldn't even be correct.

--

 

[David McRitchie]

Actually I think he was looking for something to be automatically filled in,
and for that you need an event macro,

or try to rely on "Extend lists and formats" which has so many twists and turns
that it usually doesn't work out. Though in this case it might.

I would use the following in B2 and extend it down as needed or use
an event macro to extend.
B2: =A2+OFFSET(B2,-1,0) instead of =A2+B1
the reason is so that you can easily insert or delete rows and
extend the formulas manually easily without having to modify formulas.

This is described on
http://www.mvps.org/dmcritchie/excel/insrtrow.htm
http://www.mvps.org/dmcritchie/excel/offset.htm

An Example of an Event macro to extend formula
Change Event (#change_event)
http://www.mvps.org/dmcritchie/excel/insrtrow.htm#change_event


Biff's formula works
B2: =IF(A2="","",SUM(B$1,A$2:A2))
but it is not efficient because it adding up all of column A down to the
current row for each total rather than making use of a previous sum
in the row above. The check for A2="" allows you extend the formula
down past what you need without showing, but it will extend the used range
and require printing. Not efficient would not be noticed until you have
several thousand rows or a workbook full of such formulas.
http://groups.google.com/groups?as_umsgid=ugDxrXayCHA.2592@TK2MSFTNGP10

At first I thought the original poster was seeing only galimi's reply because
ExcelForum does not thread but just attaches to the last reply in a newsgroup
thread (a minor problem compared to the more serious problems it causes), but there
were two separate replies from the original poster so he did refer to both replies,
I just think it was not understood that he would have to extend the formula from Biff down
and that an empty cell or null in column A would result in an empty column B but that
totals would still continue if more values are filled in column A later (say down two cells)..