SUM function automatically adds adjacent cells

[aj]

I'm having a problem with the SUM function that I hope someone can help
me with. I'm running OS10.3.9, with Excel 11.3. I have a row of numbers
followed by two SUM functions that each add up half the row; e.g., in
cell G4 is "=sum(a4:c4)" and in cell H4 is "=sum(d4:f4)". The problem
is that as I enter numbers, the formula in G4 automatically adds the
adjacent cells (d4:f4) to its formula, and sums all 6 cells. The second
function in H4 works, adding only the last three cells.

I tried turning off auto error checking, but that didn't work. If I
leave error checking on, I get an error saying that the formula omits
adjacent cells, but if I tell it to ignore the error, it still
auto-"corrects". Is there some setting that I'm missing that will allow
Excel to retain the formula as I entered it?

Thanks in advance for any advice.

 

[JE McGimpsey]

I'm having a problem with the SUM function that I hope someone can help
me with. I'm running OS10.3.9, with Excel 11.3. I have a row of numbers
followed by two SUM functions that each add up half the row; e.g., in
cell G4 is "=sum(a4:c4)" and in cell H4 is "=sum(d4:f4)". The problem
is that as I enter numbers, the formula in G4 automatically adds the
adjacent cells (d4:f4) to its formula, and sums all 6 cells. The second
function in H4 works, adding only the last three cells.

I tried turning off auto error checking, but that didn't work. If I
leave error checking on, I get an error saying that the formula omits
adjacent cells, but if I tell it to ignore the error, it still
auto-"corrects". Is there some setting that I'm missing that will allow
Excel to retain the formula as I entered it?

Hmmm... Are you sure there's not more going on here than "entering
numbers"?

XL will *never* change a formula when you enter values into another cell
(unless you've got an event macro which does so, but that's not really
XL's doing...).

Please give a step-by-step description of exactly what you're seeing:
the values in A4:F4 and the formulas in G4:H4 when you start, and then
after you enter a number.

 

[CyberTaz]

Two options I can think of right off:

1- Go to Excel> Prefeences> Edit, clear the check to Extend Datarange
formulas & formats - however, that might be counterproductive for other
aspects of the workbook.

2- Make the reference in the G4 formula Absolute, use Cmd+Return to finish

 

[aj]

Two options I can think of right off:

1- Go to Excel> Prefeences> Edit, clear the check to Extend Datarange
formulas & formats - however, that might be counterproductive for other
aspects of the workbook.

2- Make the reference in the G4 formula Absolute, use Cmd+Return to finish

Thanks. Both these options work. I had read the description of Extend
List Formats and Formulas, but it didn't seem to apply. Obviously it
does. It's not clear to me why it should extend formulas to cover
additonal cells in the same row; I thought the point of the automatic
extension was for adding a new row to a list, so that the new row would
have the same formats and formulas as existing rows. In any event, the
problem seems solved, and I thank you again.

-aj

 

[aj]

Hmmm... Are you sure there's not more going on here than "entering
numbers"?

XL will *never* change a formula when you enter values into another cell
(unless you've got an event macro which does so, but that's not really
XL's doing...).

Please give a step-by-step description of exactly what you're seeing:
the values in A4:F4 and the formulas in G4:H4 when you start, and then
after you enter a number.

Thanks for replying.

It happens even with only one SUM function. Start with a blank
worksheet. Enter "=sum(a4:c4)" in cell G4. Then select A4 and enter
"1". G4 correctly says "1". Now go to B4 and enter another 1. G4 is now
2. Similarly enter 1 in C4, and G4 is now 3. Now enter 1 in D4. G4
turns black for a moment, and then reads 4! If you check cell G4, the
formula now reads "=sum(a4:d4)". If you enter a 1 in E4, G4 will now
have 5, and the formula reads "=sum(a4:e4)".

-aj

 

[CyberTaz]

I'm glad the suggestions helped, but the actual reason that the problem
occurred is that you wrote a formula (or two) that referred to empty
cells In days gone by that was a no-no, but today's "user-friendly,
intuitive" software let's us get away with doing [actually *encourages* us
to do] things that lead to problematic situations

Also: You're right that a "list" is normally columnar in most people's
minds, but as you've now found it can be horizontal as well... At least in
the mind of Excel.

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[JE McGimpsey]

It happens even with only one SUM function. Start with a blank
worksheet. Enter "=sum(a4:c4)" in cell G4. Then select A4 and enter
"1". G4 correctly says "1". Now go to B4 and enter another 1. G4 is now
2. Similarly enter 1 in C4, and G4 is now 3. Now enter 1 in D4. G4
turns black for a moment, and then reads 4! If you check cell G4, the
formula now reads "=sum(a4:d4)". If you enter a 1 in E4, G4 will now
have 5, and the formula reads "=sum(a4:e4)".

Ah, you've got Extend list format and formulas set in Preferences/Edit.

I should have mentioned that above. Glad Bob caught it...