Yogi Anand, D.Eng, P.E. Google Spreadsheet www.energyefficientbuild.com
lunixer said:
How do I sum an entire column up to the row where I am and keep this formula consistent in later rows?
Okay, here's what I want to do:
In cell L3 I want to put the formula: =K3/SUM(C2:C3)
In cell L4 I want to put the formula: =K4/SUM(C2:C4)
Then I want to be able to paste this same formula throughout the entire L column, so that the summation always goes from C2:C# where # is equal to L#. However, when I paste this formula, eg from L3 to L5, it changes it to be L5=K5/SUM(C4:C5). And when I highlight the first two cells with this formula and drag, it maintains a three cell difference. So L6=K6/SUM(C4:C6). Do you have any idea how I would make this automatic?
---------------------------------------------------------
lunixer said:
How do I sum an entire column up to the row where I am and keep this formula consistent in later rows?
Okay, here's what I want to do:
In cell L3 I want to put the formula: =K3/SUM(C2:C3)
In cell L4 I want to put the formula: =K4/SUM(C2:C4)
Then I want to be able to paste this same formula throughout the entire L column, so that the summation always goes from C2:C# where # is equal to L#. However, when I paste this formula, eg from L3 to L5, it changes it to be L5=K5/SUM(C4:C5). And when I highlight the first two cells with this formula and drag, it maintains a three cell difference. So L6=K6/SUM(C4:C6). Do you have any idea how I would make this automatic?
---------------------------------------------------------
comment from lunixer:
ReplyDeleteThank you very much. I just have one question. Your formula seems to use the sum of all of the rows up to and including the next row when summing an entire column. For example, for my first row, in which C2=0 and K2=0, your formula returns 4.3. In my second row, C3=.93 and K3=4, so it should be 4.3 for the second row. The last row has L37=-- for the same reason. Is there any way to fix this and offset each row by one? Thank you very much for your help!
response from Yogi:
ReplyDeleteI am not clear on what you have said or what you want ... but in Sheet2 of my Blog post, I have moved the fromula fom cell L3 to cell L2 -- check it out to see if this is what you are looking for.
Cheers!
Yogi