incorrect totals

G

Garf

I have a date selection for 13 weeks and have split the weeks in to months
like below

28-Sep
05-Oct
12-Oct
19-Oct
26-Oct
02-Nov
09-Nov
16-Nov
23-Nov
30-Nov
07-Dec
14-Dec
21-Dec

October 0
November 0
December 0

Qtr 4 0

The problem I have is sometimes when i enter data in the weeks the october
sum calculates all 13 weeks whereas the nov and dec stay in their set
parameteres, I could just apply absolutes but its an extremely long job, is
there anything i can turn off that makes it self calculate?
 
D

David Biddulph

You haven't told us what formula you are using to calculate your totals, so
it is stretching the ability of the clairvoyants in the group to explain
what you've done wrong.
 
G

Garf

Hi David its just a simple sum i.e. October =sum(b2 - b6), Nov b7 - b10, Dec
b11 -b14 and so on but when i enter data for week 6 for example it apples it
to the October formula and when i go back to check it it has reset the
october formula to sum b2 b13, but nov and dec stay as they should, wierd
 
D

David Biddulph

Well =sum(b2 - b6) won't give you a total of B2 to B6.
The hyphen works as a minus sign, so B2-B6 will subtract the value in B6
from the value in B2.
The SUM function tries to add a range, or a series of arguments, but you've
only given it one argument (B2-B6) so you haven't told it what to add to
B2-B6, and hence the result will simply be the value of (B2-B6), just the
same as if your formula had been =B2-B6
If you want the total of B2 to B6, the formula is =SUM(B2:B6).

If Excel is changing your formula as you add data to a range, look at Tools/
Options/ Edit, and deselect "Extend data range formats and formulas".
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top