Formula

[confused in florida]

Locatiion Daily Sales MTD Sales
2 5625 10025
4 2240 7261
6 1789 4381
8 3427 8425
Question:
I need to update this spreadsheet daily by changing my daily location
figures and automatically updating my MTD figures. I keep getting an error
message that I have created a circular formula. What formula is needed to
automatically have the values in the MTD column calculate?

 

[Linc]

You should probably put your daily figures in separate columns, labeled
"1" to "31". If your daily figures begin in column C, then your MTD
formula in, say, B2 is:

=sum(C2:AG2)

Otherwise, you'll need to use a macro to add the daily figures to the
MTD figures.

 

[confused in florida]

My locations are in column A, Daily Sales for each location are in column B,
and MTD sales are in column C. Each day when I update the daily sales for
each location i have to use the calculator to manually update the MTD sales
by adding previous day's MTD sales to today's daily sales. I am looking for a
formula that will update the MTD sales automatically when i enter the daily
sales into the spreadsheet. Any suggestions?

 

[Linc]

You cannot use formulae in the cells to do what you'd like as you will
create a circular reference. You already know this, of course.

You could put your daily sales figures into 31 columns in another sheet
and refer to them from your MTD cells, if you have the columns after C
populated with other information. (I'm assuming 31 sales days. If your
sales are on a Monday-through-Friday basis, you won't need as many
columns.) This has the advantage of enabling you to correct mistakes;
if you put in sales of, say, 6378 instead of 3678 for a particular day
you can see that later on and enter the correct value. Doing it the
way you describe means that the MTD figures cannot be easily
reconciled, and you have to trust yourself to always enter the correct
figures. I don't know about you, but I wouldn't ever trust myself to
do that. We're only human.

To get the behavior you describe would take some VBA code that
determines when a number has been entered in a daily sales cell, adds
that figure to the MTD figure, then deletes the daily figure. With all
due respect, it's far easier and much more transparent to do all this
calculating right on the spreadsheet. And, as I stated above, far
easier to correct errors in data entry.