Excel =SUM question.

4030 Views 28 Replies 9 Participants Last post by Work2Painting, Dec 31, 2013

Leo G

Discussion Starter · Nov 22, 2013 (Edited)

OK, here's the short story. I run an Excel spreadsheet to do my quarterlies. I have a copy of this on my Galaxy S4 and use Kingsoft Office to open it and modify it. All has been well up until today or recently because I'm not sure when the issue started.

In my phone my SUM functions weren't recalculating. I would enter a number in the field and the SUM would remain the same.

They were working in my laptop using Excel 2003.

One of the SUM functions is written as =SUM(B5:B102), so it adds up the numbers in the column B, cell 5-102.

After fooling around with it for a while and trying different things I came up with a workaround.

=SUM($B5:$B102)

OK, I have no idea what this change is/does. I only know that it solved my problem.

Anyone know what this version of the SUM function I am using does?

Save

1 - 2 of 29 Posts

jproffer

· #17 · Nov 22, 2013

TimelessQuality said:
Only if you insert columns or lines...

That's not true.

If you make the formula =SUM($B$2:$B$20) and insert a column between A and B, the formula changes to =SUM($C$2:$C$20) ...where column C was originally column B.

If you make the formula =SUM($B$2:$B$20) and insert a row between 1 and 2, the formula changes to =SUM($B$3:$B$21) ...where row 3 was originally row 2.

Relative cell references only change if you drag the formula around.

Now, all that said....I thought what you said was true. I guess it's never come up before (hard to believe with the amount of excelling I do, but **shrug**). So I checked, and tested what I just typed out.

One more caveat

...I don't know if this is an application level option that you could change to "Not update cell references on row/column insert or delete", or if "that's just the way it is...it's not an option". I didn't look through the options.

Save

Reply Quote

jproffer

· #19 · Nov 22, 2013 (Edited)

I don't know anything about Kingsoft...it could be because SUM is an excel function....so maybe Kingsoft isn't recognizing that...??

I guess if it remains a problem, you could change the formula to:

=B5+B6+B7+B8+B9.......+B100+B101+B102

If you have to do it too many places, I would type that out in Word or something, and then C/P it into whatever cells you need it in.

I still can't imagine why making the references absolute ($) would make a difference. You would think if Kingsoft recognizes that, it would recognize a relative reference as well.

EDIT: Didn't take as long as you would think LOL. (It's a slow day, what can I say). Doing in excel was faster BTW.....+(click)+(click)+(click)+(click)+(click)+(click)+(click)+(click)

Anyhow, if you're interested...

=B5+B6+B7+B8+B9+B10+B11+B12+B13+B14+B15+B16+B17+B18+B19+B20+B21+B22+B23+B24+B25+B26+B27+B28+B29+B30+B31+B32+B33+B34+B35+B36+B37+B38+B39+B40+B41+B42+B43+B44+B45+B46+B47+B48+B49+B50+B51+B52+B53+B54+B55+B56+B57+B58+B59+B60+B61+B62+B63+B64+B65+B66+B67+B68+B69+B70+B71+B72+B73+B74+B75+B76+B77+B78+B79+B80+B81+B82+B83+B84+B85+B86+B87+B88+B89+B90+B91+B92+B93+B94+B95+B96+B97+B98+B99+B100+B101+B102

You have a few fixes to do...some of the numbers "came apart" when copying from there to here.

Save

Reply Quote

1 - 2 of 29 Posts

This is an older thread, you may not receive a response, and could be reviving an old thread. Please consider creating a new thread.

Top Contributors this Month