Simple (?) Excel formula troubles

  Mike D 16:29 19 Mar 2010

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 2010

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 2010

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

  John B 17:29 19 Mar 2010

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 2010

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

  Mike D 17:37 19 Mar 2010

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 2010



copied down. This fixes the columns rather than rows.

  Mike D 17:51 19 Mar 2010

Thanks very much.


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

Elsewhere on IDG sites

Samsung Galaxy S9 review

Explore Milton Glaser's iconic poster designs from the 1960s to present, including Bob Dylan, I…

What to expect at Apple's 27 March education event

Idées cadeaux pour geeks et tech addicts