Automating Tables

Brew

Well-known Member
Joined
Sep 29, 2003
Messages
1,569
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
ECEDEEEFEGEHEIEJEKEL
21E/O(ColumnU)D/S(ColumnAV)
223210123
23Last10Last10
24%%
25Last100Last100
26%%
27
28
29H/Low(ColumnEM)
303210
31Last10
32%
33Last100
34%
combo
PC Pick3 System Chart3.xls
EMENEOEP
51
52
532
54
552
56
571
58
590
60
611
62
631
64
combo
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Brew

Well-known Member
Joined
Sep 29, 2003
Messages
1,569
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

Well-known Member
Joined
Sep 29, 2003
Messages
1,569
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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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
BCDEFGHIJ
1CountLast
210FALSEFALSE
3numericentries
4
5entryentryNumberOf3210
6blank34312
7text1
823
932source
100criteria
1112coppyto
1233
13text0
1413
15text2
163
172
180
192
203
210
223
232
Sheet3


I hope this helps!
 

Brew

Well-known Member
Joined
Sep 29, 2003
Messages
1,569

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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

Well-known Member
Joined
Sep 29, 2003
Messages
1,569

ADVERTISEMENT

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

Well-known Member
Joined
Sep 29, 2003
Messages
1,569
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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Brew said:
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

Well-known Member
Joined
Sep 29, 2003
Messages
1,569
Thanks Yogi for the update.
Yes, I need further help to develop these 2 remaining steps you suggested.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,481
Messages
5,601,918
Members
414,482
Latest member
morkar

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