Simple (?) Excel formula troubles

  Mike D 16:29 19 Mar 10
Locked

Help, my sparse hair is becoming even more endangered.
We are using Excel XP. Using Autosum I get a total for A1:D1 in I1 and a total for E1:H1 in J1. Simples but, after entering values in A1,B1,C1 and D1 and getting the total in I1, a value entered in E1, F1, G1 or H1 is not only totalled in J1, but is also added onto the original total in I1. If values are entered into E1:H1 before entering values in A1:D1 everything works fine. However we do not always get the second group of values for entering before the first group are available.

What is even more puzzling is that for the first 100 or so rows it all worked properly but now, whether we copy the formulae down or enter a new formula for each row we get the same problem.

  MAJ 16:55 19 Mar 10

Don't use Autosum, it will take in all values from A1 to I1. Use =SUM(A1:D1) in I1 and =SUM(E1:H1) in J1. If I've read you correctly that is.

  Mike D 17:00 19 Mar 10

Tried that, it makes no difference. this is what is so frustrating.
Thanks any way.
Mike

  John B 17:29 19 Mar 10

I just tried and what seems to happen is Excel modifies the formula in I1 to include a greater range. When I corrected the formula it stayed the same afterwards.

I wonder if using the $ symbol would help?

  John B 17:32 19 Mar 10

=SUM(A$1:D$1) seems to work

  Mike D 17:37 19 Mar 10

Doesn't seem to help because, whilst it lets you keep to two ranges for the first row, the formulae cannot be copied down because of the absolute cell references, which means that the formulae in each row have to be typed in seperately - we are currently on row 249!

  VoG II 17:41 19 Mar 10

Try

=SUM($A1:$D1)

copied down. This fixes the columns rather than rows.

  Mike D 17:51 19 Mar 10

Brilliant!
Thanks very much.

Mike

This thread is now locked and can not be replied to.

Nintendo Switch review: Hands-on with the intuitive modular console and its disappointing games…

1995-2015: How technology has changed the world in 20 years

Method Studios' title sequence for BBC series Taboo is truly unsettling

Best Pages for iOS tips | How to use Pages for iPad & iPhone: 6 simple tips to get more out of…