Averages and updating

buffalo

Board Regular
Joined
Jun 19, 2003
Messages
183
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
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

buffalo

Board Regular
Joined
Jun 19, 2003
Messages
183
Hi thanks,

I forgot to mention I want a VBA code which will automatically do the above
whenever a value is entered in Col B..

THanks
 

Watch MrExcel Video

Forum statistics

Threads
1,122,334
Messages
5,595,567
Members
413,996
Latest member
mabelO

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top