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.

  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!

  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

Pokémon Let's Go, Pikachu! and Eevee! review

Best Black Friday deals for designers and artists 2018

How to use Siri Shortcuts

Les meilleurs jeux Nintendo Switch (2018)