how to keep the total colum

E

EXCEL Girl

I have 3 columns, 1 for the current amount, the 2nd for the previous and the
3rd for the total. The following week, the current week column will become
the previous week. Meaning I will have to move the information over to the
2nd column and I will input new numbers into colum one for the "new current
amount". My problem is once I change the information in colum 1, the "total"
colum will also change. My problem is, I need this to be ongoing for the
entire month. Hope this make sense
 
R

Roger Govier

Hi

If you change your data layout slightly so that
COLUMN B = TOTAL
COLUMN C = PREVIOUS
COLUMN D = where you enter current data

in B2
=SUM(INDEX($2:$2,1,3):INDEX($2:$2,1,4))
In C2
=SUM(INDEX($2:$2,1,5):INDEX($2:$2,1,255))
Copy down as required
When you have finished entering current weeks data in column D, Insert a
new column at D to input the next week's data.
 
E

EXCEL Girl

The information you supplied was somewhat helpful but it’s still not where
I’m trying to go.

I think this info will help you understand more of what I'm trying to do:

- For example: The MTD total for Failed Customer Service total is 5 (weeks
1 & 2).
- Let’s say we are coming into the 3rd week. All of the information from
the current interactions will become the previous week interactions in this
case; the number will be 1 for Failed Customer Service (the new previous
week).

- The 3rds weeks MTD total for Failed Customer service will be 5 + new
number for the 3rd weeks number of failed customer service.

o Lets say for example the number is 2.

o Therefore the new MTD for Failed Customer service will be will 7.

I need for the MTD column to retain the last’s weeks MTD total and then I
need to add the new current information to that total. (i.e. this week 2 + 5
=7….Next week ( New number of Failed Customer Interactions + 7 = New MTD
Column).
 
R

Roger Govier

Hi

I thought that is what I was achieving, other than my Previous total was
for all weeks from 1 week ago up to potentially 250 weeks ago.

On a new blank sheet, set up the formulae in the columns as per my
previous post.
In cell E2 enter 1, in F2 enter 4
Condition A
Now cell B2 Total will equal 5
cell C2 Previous will equal 5
cell D2 This week will equal nothing
Condition B
Enter 2 in cell D2 (this week's value)
Now cell B2 Total will equal 7
cell C2 Previous will equal 5

Insert a new column at D, by Right clicking on the column heading and
choosing Insert
Condition C
Now cell B2 Total will equal 7
cell C2 Previous will equal 7
cell D2 This week will equal nothing
Because, at that point, when you insert new blank week, the cumulative
total will equal the previous weeks cumulative total

If you only want to accumulate up to 5 weeks data, (this week plus 4
weeks previous) then change the formula in C2 to
=SUM(INDEX($2:$2,1,5):INDEX($2:$2,1,8))
 
E

EXCEL Girl

Thanks Roger,

Your formula is to compare this weeks accumulating total to the previous
weeks accumulating total. However, I'm wanting to compare the number of
interactions this week to the number of interactios from previous week. and
afterwhich, I want to add the number of this weeks activities to the
accumulating total.
 
R

Roger Govier

Hi

In column B you see the Total including this week's entries
(adjusting as more entries are made to this week)
In column C you see the Total up to last week
in Column D you see this weeks figure
in column E you see the week before's figure
in column F you see the figure for the week before that, and so on.

What else do you need to see?
 
Top