Hi,
I'm having this problem since yesterday but haven't gotten a simple solution yet.
Suppose I have Col A and Col B with some data.
Col A Col B
AAAA 4
AAAA 5
BBBB 4
AAAA 5
DDDD 23
CCCC 3
AAAA 6
CCCC 3
etc...
Now in Col C and D, I make a "table" to calculate the average of each entry in Col B corresponding to entries in Col A, for e.g.
AAAA 5 (because 5 = (4+5+5+6)/4 , etc...)
BBBB 4
..etc
QUESTION:
I need to do some manipulatations b/w the "previous average" and "present value entered"
For instance,
Next I update Col A and Col B with a new entry, say AAAA and 10 respectively
So in the above example, at this point in time:
"previous average of AAAA" = the avg of AAAA before this new value was entered = 5
"present value entered" = 10
As soon as I enter that the current avg of AAAA will change to 6, and I will have lost the previous avg.
** How would I "store" the "previous average" of 5? **
This has to be generic and at every step. So the current avg is actually used as the previous avg when, for instance, AAAA and 18 are entered in Col A and B respectively.
Now the current avg = 8, and the previous avg = 6 and so on...
Ideally I would like to have 2 tables, one with current avg values and the adjacent one with previous avg values that keep updating. How would I do this? My method is very long...
Any other suggestions are welcome.
NOTE, I already have the averages, all I need is a way to STORE the previous average in a table somewhere. Everytime there is a change, in Col A and B, both the corresponding averages "update".
thanks
I'm having this problem since yesterday but haven't gotten a simple solution yet.
Suppose I have Col A and Col B with some data.
Col A Col B
AAAA 4
AAAA 5
BBBB 4
AAAA 5
DDDD 23
CCCC 3
AAAA 6
CCCC 3
etc...
Now in Col C and D, I make a "table" to calculate the average of each entry in Col B corresponding to entries in Col A, for e.g.
AAAA 5 (because 5 = (4+5+5+6)/4 , etc...)
BBBB 4
..etc
QUESTION:
I need to do some manipulatations b/w the "previous average" and "present value entered"
For instance,
Next I update Col A and Col B with a new entry, say AAAA and 10 respectively
So in the above example, at this point in time:
"previous average of AAAA" = the avg of AAAA before this new value was entered = 5
"present value entered" = 10
As soon as I enter that the current avg of AAAA will change to 6, and I will have lost the previous avg.
** How would I "store" the "previous average" of 5? **
This has to be generic and at every step. So the current avg is actually used as the previous avg when, for instance, AAAA and 18 are entered in Col A and B respectively.
Now the current avg = 8, and the previous avg = 6 and so on...
Ideally I would like to have 2 tables, one with current avg values and the adjacent one with previous avg values that keep updating. How would I do this? My method is very long...
Any other suggestions are welcome.
NOTE, I already have the averages, all I need is a way to STORE the previous average in a table somewhere. Everytime there is a change, in Col A and B, both the corresponding averages "update".
thanks