Automating Tables

[Brew]

I would like to program 3 tables within excel that automatically updates the following data:

in U column
how many 3s, 2s 1s, and 0s in the last 10 entries and the percentages for each.
Also, how many for the last 100 entries and their percentages.
I have a 2nd column (column EM) that produces 0s, 1s, 2s and 3s. I need the last 10 entries and the percentages for each.
Also, how many for the last 100 entries and their percentages.
I would like to do the same for the data in a 3rd column (AV column) which has only 1s, 2s and 3s.

Some cells in the columns are blank, so ingnore those as non-entries.

PC Pick3 System Chart3.xls
	EC	ED	EE	EF	EG	EH	EI	EJ	EK	EL
21	E/O(ColumnU)						D/S(ColumnAV)
22		3	2	1	0			1	2	3
23	Last10						Last10
24	%						%
25	Last100						Last100
26	%						%
27
28
29	H/Low(ColumnEM)
30		3	2	1	0
31	Last10
32	%
33	Last100
34	%
combo

PC Pick3 System Chart3.xls
	EM	EN	EO	EP
51
52
53	2
54
55	2
56
57	1
58
59	0
60
61	1
62
63	1
64
combo

 

[Brew]

In otherwords,
I want the problem the total of #3, #2, #1 and #0 in the U column since the last
10 and 100 numerical entries in the column.
example:
last 10: ED23=2, EE23=5, Ef23=3, EG23=0
% ED24=20%, EE24=50%, Ef24%=30, EG24=0%
last 100: ED25=19, EE25=30, Ef25=30, EG25=21
% ED26=19%, EE26=30%, Ef26%=30, EG26=21%

Maybe, a couple of formulas will work, that would look at the entire column
for the last 10 or 100 numerical entries and return how many are 3, 2, 1 or 0 numbers
are in both categories (10 and 100 entries).

 

[Brew]

I want the problem the total the number of #3, #2, #1 and #0 in the U column since the last 10 and 100 numerical entries in the column.
example:
...................3................2................1...............0
last 10:.....ED23=2,......EE23=4,......Ef23=3,........EG23=1
%..........ED24=20%,.EE24=40%,..Ef24%=30,....EG24=10%
last 100:...ED25=19,.....EE25=30,....Ef25=30,......EG25=21
%..........ED26=19%,..EE26=30%,..Ef26%=30,...EG26=21%

Example of last 10 numerical entries, relating to the above example
Column U
U13= blank
U14= blank
U15= 3
U16= blank
u17= 2
u18= blank
u19= 2
u20= blank
u21= 1
u22= blank
u23= 3
u24= blank
u25= 1
u26= blank
u27= blank
u28= 1
u29= blank
u30= 0
u31= blank
u32= 2
u33= blank
u34= 2

Maybe, a couple of formulas will work, that would look at the entire column
for the last 10 or 100 numerical entries and return how many are 3, 2, 1 or 0 numbers are in both categories (10 and 100 numerical entries).

 

[Yogi Anand]

Hi Brew:

In the following illustration I have first used Advanced Filter to first filter the source data for the last 10 numeric entries and then counted the number 0f 3's, 2's, 1's and 0's ...

Book2
	B	C	D	E	F	G	H	I	J
1	CountLast
2	10		FALSE	FALSE
3	numericentries
4
5	entry		entry		NumberOf	3	2	1	0
6	blank		3			4	3	1	2
7	text		1
8	2		3
9	3		2			source
10			0			criteria
11	1		2			coppyto
12	3		3
13	text		0
14	1		3
15	text		2
16	3
17	2
18	0
19	2
20	3
21	0
22	3
23	2
Sheet3

I hope this helps!

 

[Brew]

Yes this the results, I am looking for place upon the sample data you have illustrated, but it's a little cumbersome, and still has to be manually updated will I add a new row.
I want the table to update every time I add a new row or two.
I am only adding one row of data at a time. Also some of the cells in the column are blank or empty and I will probably have to look at the last 25 rows or less to get the last 10 numerical entries and 220 rows to get the last 100 numerical entries, then count how many of those are the number 3, 2, 1, and 0 exist.

So if I have 150 rows, I may have to go back 22 to 25 rows back to get the last 10 numerical entries to calculate, which would possibly be 125 to 150, then calculate. Next, I add 2 rows with one row of data and the other empty, My table should update by calculating going back to rows 129 to 152 to get the nesxt last 10 numerical entries.

I want the problem the total the number of #3, #2, #1 and #0 in the U column
since the last 10 and 100 numerical entries in the column.
Below is an example of the table I want to automatically update after every numerical entry:
....................3.................2...............1..................0
last 10:.....ED23=2,;......EE23=4,......Ef23=3,...........EG23=1
%..........ED24=20%,. EE24=40%,.. Ef24%=30,....EG24=10%
last 100:... ED25=19,.....EE25=30,.... Ef25=30,....... .EG25=21
%..........ED26=19%,.. EE26=30%,.. Ef26%=30,... EG26=21%

Example of last the 10 numerical entries, relating to the above example
Column U
U13= blank
U14= blank
U15= 3
U16= blank
u17= 2
u18= blank
u19= 2
u20= blank
u21= 1
u22= blank
u23= 3
u24= blank
u25= 1
u26= blank
u27= blank
u28= 1
u29= blank
u30= 0
u31= blank
u32= 2
u33= blank
u34= 2

Maybe, a couple of formulas or function will work, that would look at the entire column
for the last 10 or 100 numerical entries and return how many are 3, 2, 1 or 0
numbers are in both categories (10 and 100 numerical entries). Then give the percentages.

 

[Yogi Anand]

Hi Brew:

I would suggest that you first try to incorporate the formulations I suggested into your actual ranges, and the number of numeric entries to be counted.

Once everything is working the way you intend it to work, then you can look into automating the process.

 

[Brew]

Thanks Yogi, I've incorporated your formulations to my actual ranges and the numeric enteries to be counted. It works as you illustrated. I see how to repeat it for all of my remaining table calculations.
I have 2 remaining questions:
I did not add your legend (source, criteria and coppyto with its colors). Is it critical to calculations or a legend to use as a reminder.
Also, now that everything is working the way I intend it to work, how do I automate the process, so that the update update everytime a row or two is added.

 

[Brew]

Now that I have Yogi's formulation setup. I need a function probably that
will automatically update the coppyto data list and the number of 3, 2, 1, and 0 table,
whenever an additional numeric entry is added to another row in the entry column cells.
This is the last 10 numeric and last 100 numeric entries.

...................3................2................1...............0
last 10:.....ED23=2,...... EE23=4,......Ef23=3,........EG23=1
%..........ED24=20%,. EE24=40%,..Ef24%=30,....EG24=10%
last 100:...ED25=19,.....EE25=30,.... Ef25=30,......EG25=21
%..........ED26=19%,..EE26=30%,..Ef26%=30,... EG26=21%

 

[Yogi Anand]

Thanks Yogi, I've incorporated your formulations to my actual ranges and the numeric enteries to be counted. It works as you illustrated. I see how to repeat it for all of my remaining table calculations.
I have 2 remaining questions:

I did not add your legend (source, criteria and coppyto with its colors). Is it critical to calculations or a legend to use as a reminder.

This is only legend -- and as you have stated to be used as a reminder

Also, now that everything is working the way I intend it to work, how do I automate the process, so that the update update everytime a row or two is added.

To automate it we have to ...

1. create and use dynamic range(s) instead of static range(s)
and
2. create a Worksheet_Change event that will automatically execute the AdvancedFilter when you add a numeric entry and then do the affiliated calculations

I hope this helps. And if you want to discuss this futher -- please post back and then let us take it from there.

 

[Brew]

Thanks Yogi for the update.
Yes, I need further help to develop these 2 remaining steps you suggested.