[Leo G]

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?

 

[wallmaxx]

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?

No idea as of yet but i will look into it.

I typically see a sum string as

=SUM(B5:B102)

colon instead of comma to define a range of cells

 

[Leo G]

Sorry, it is a colon. My mistake.

Edited original post to correct

 

[KennMacMoragh]

Dollar sign in the formula means it's an absolute cell reference. If you try dragging the formula to column C, you would still get the sum of column B. Without the dollar signs, you would get the sum of column C by dragging the formula over to it.

It sounds like your original problem was a glitch in the program, if you tried copying and pasting all your formulas to a new document maybe it would fix it.

 

[hdavis]

Keen has it.

I use the absolute reference to prevent cell reference changes when I use copy or fill functions building a spreadsheet - filling automatically changes them, incrementing each cell reference as it fills.

You can make the row or column cell reference absolute separately with it like A$13 , $A13 , $A$13. In a copy and paste or fill function, whatever doesn't have the $ will get incremented.

 

[lapcon]

Auto Recalculate?

An issue may be that your program is not automatically recalculating. This function can be turned on and off.
So if auto recalc is off, then the sum function will not execute.
Hope this helps.

 

[hdavis]

Good tip, I had forgotten about that.:thumbsup:

 

[Leo G]

An issue may be that your program is not automatically recalculating. This function can be turned on and off.
So if auto recalc is off, then the sum function will not execute.
Hope this helps.

That was a good point. But I don't think my phone app has that function. It has a recacl button but no options other than that.

It seems to be this spreadsheet file only as I have a couple of others that use a very similar format and setup as this one and they work.

If I transferred it from my phone to my laptop which is running Excel 2003 it functions fine. If I transfer it back to my phone it stops recalculating.

So now that I have this absolute function in my formula's (I've changed the whole spreadsheet to use them), is there going to be problems with my formulas?

 

[TimelessQuality]

Only if you insert columns or lines...

 

[Leo G]

And another item. I had made a blank of this spreadsheet because I use it to do my quarterlies. And when the next year comes I just change the date and start over.

When I transferred that file to my phone it didn't work either. Changing it to the absolute SUM makes it function properly again.

 

[Leo G]

Only if you insert columns or lines...

I guess that's gonna be a problem then, because I do insert on occasion.

 

[Leo G]

There seems to be several flavors of this function. Can you tell me what each of them does?

=SUM(B5:B102) This is standard formula
=SUM($B5:$B102) This is the absolute cell formula
=SUM(B$5:B$102) ??
=SUM($B$5:$B$102) ??

 

[TimelessQuality]

There seems to be several flavors of this function. Can you tell me what each of them does?

=SUM(B5:B102) This is standard formula
=SUM($B5:$B102) This is the absolute cell formula
=SUM(B$5:B$102) ??
=SUM($B$5:$B$102) ??

1- Column,Row to Column,Row ... relative to the function.. if i you insert rows or columns, it will adjust to stay the same 'relatively'

2 - Locks only the column, absolutely will always be column B even if you insert a new in front

3 - Locks only the row

4 - Locks both

 

[Leo G]

 

[Leo G]

Since I'm using the lock column version of it. If I have column B-K used and I make/insert another column "L" then I have nothing to worry about, correct?

And this gives me the option to insert new rows if I want to and it won't screw anything up, correct?

My rows are Date them a dollar figure in the appropriate column. On occasion I miss an entry and then insert the missed date in the correct position and then make the entry. This will still function properly?

 

[TimelessQuality]

That should be OK

 

[jproffer]

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.

 

[Leo G]

I'm operating my file from two different programs. And although the Kingsoft Office is pretty complete the laptop version has more options.

Never knew I could turn recalc on and off.

I really don't understand why the phone app has the recalc button when there is no option to turn it off. I assume it's because you might import a file from a computer that has the recalc turned off and if you go to the phone app you wouldn't be able to use the program.

And I did try to use the recalc button before I swapped my SUM formulas to absolute, it didn't work.

 

[jproffer]

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.

 

[KennMacMoragh]

I use google drive for my tablet, pc, and phone. Spreadsheet syncs instantly between all of them. You might want to try it, it's a lot easier than constantly transferring files around.