MrExcel Publishing
Your One Stop for Excel Tips & Solutions

additon with unknown range


Posted by dee'd ferman on February 07, 2002 7:58 AM

ok, i am pretty new with excel, and i figure this is something that is pretty easy, but i just have not been able to find the answer.

On a spreadsheet, column c is dollar amounts. column d is the answer of column c divided by two.

how do i keep a running total of these? these will be kept monthly, and the number of entries each month will vary, from 90 to 250, depending on how busy we were.


Posted by Todd on February 07, 2002 8:12 AM

Assuming the values begin in b2, enter this formula in d2: =sum(c$2:c2)
Then fill down.
For example, cell d7 should be =sum(c$2:c7)

Posted by dee'd ferman on February 07, 2002 8:36 AM

okay...so the $ works as a wild card?

Posted by Todd on February 07, 2002 9:44 AM

no, the $ freezes the element. For example, if you put =$B$2 in cell c2 and fill down, all the cells will have the same formula. However, if you but =$B2 and fill down, the cells will differ. If you filled right instead, the cells would be the same. If this confuses you, just experiment on a worksheet.

Posted by dee'd ferman on February 07, 2002 10:07 AM

ok...understand that part. now, currently, i wait until i know the month is ended. for example, the last entry is line 137. so for column c, i would go to line 139, and put in the formula =sum(c5:c137) and in column d i would put the formula =c137/2 however, next month, i may have the last entry on line 92.

is there a way that it will fill in the last entry line for me? or will i always have to do this manually?


Posted by Todd on February 07, 2002 10:11 AM

have you tried mark W's answer to your other post?
Alternatively, you could put the sums at the top of the column, like in c2, then have the data beginning at c4 and on down. Your formula in c2 could be =sum(c2:c500)

Posted by dee'd ferman on February 07, 2002 10:20 AM

i did not realize that this got posted twice...my system crashed during the first one, so i figured it was gone...duh...anyway, thank you very very much, i think i have it figured out now!! and thank you for being patient with my dumb questions and ignorance