Hey all, hopefully someone can help. This should be easy but is kicking my butt
The reason for this is that it
appears that we have some people fudging measurments over the past several years of data and that it may be ongoing. If they
are not fudging then we have some external factor seeming to create trends in our data that we have to identify as some of it
is too uniform.
I need to find several things and I believe it all hinges on the counting the number of times a
list of numbers appears in a table of data. Pivot tables are not working for me on this.
The data is in Column A
to Column G in my worksheet
The columns are headed Date, M1, M2, M3, M4, M5, M6
The Date column contains the DAY and DATE both as text. (wednesday, march 16, 1993)
Columns headed M1 through M5 contain the numbers 1 through 80
Column headed M6 contains the numbers 1 through 60
Each row of data contain unique numbers in M1 through M5 (columns B,C,D,E, and F)
The first thing I need to figure
out is how to count and output every number in the range 1 to 80 appears in columns M1 through M5. so in the entire range of
data in columns headed M1 to M5 how many times does 1 appear, 2 appear, 3 appear, etc all the way through 80.
There are thousands of rows of data (and it grows every day) so doing COUNTIF for each of the 80 numbers as individual
entries is not practical.
So the first question is how do I pass the numbers 1 to 80 to the function and out put
them to a new column or table?
What i need is to create a table (or set of columns) that say "Measurment X
appears" ?? "times" and automatically updates as I enter each new data row to the table. This is for each of
the 80 possible numbers.
Next I need to count the number of times that each number 1 through 50 occurs in Column G
(headed M6) and that updates automatically as I enter each new data row.
I also have to calculate how often the
numbers appear in 'groups'.
Meaning how often does any specific number appear in the same row as any other specific number or numbers.
how often do 1, 34 and 56 appear in the same row, this has to be for all possible 5 number combinations of 1 through 80.
No numbers in columns headed M1 through M5 will be duplicated on the same row, but the value in column headed M6 might
duplicate on the same row with a number form M1 to M5. For example M3 might have 24 and M6 might also have 24 on the same
Then I have two more calculations to do.
The first is using the DAY values Monday through Sunday (7 days) how often does each of those numbers occur on each day. For
example on Mondays the number 15 occured 29 times in the range of data. I need this for every number in M1 to M5 (1 to 80)
for every DAY Monday through Sunday. I also need this for MG.
Second, the 'groups' found in the previous
calculations have to be checked against the same DAY frequency calculations. For example if a group (1 and 56, and 72) occurs
more than ONCE in M1 to M5 how often did it occur on Mon, Tues, Weds, etc.
I appreciate any input I can get as
even the first step has me stumped at this point.