I need a formula to assist me in counting instances where two values occur
together in a row through a specific range of cells. To put it simply, let's
say the numbers 1 through 20 are numbers that are returned from an array of
sensors tuned to those specific values, and I have 5 to 10 sensors returning
a stream of data. I have created a table with x and y axes both running 1 to
20, and want the intersecting cells to tell me how many times number from
axis y occurs with number from axis x. (I have built my table with
conditional formatting to gray out cells that intersect matching numbers, 1
and 1, etc.)

eg:
01 02 03 04 05 06 07 (...etc.)
01
02
03
04
05
06
(...etc.)

Been pouring through functions, examples, help files, and questions on here.
Have found nothing yet that is close to what I need. Anybody have an idea?

Sounds like you need some sort of INDEX/MATCH formula but your explanation is
a little difficult to follow - where will the data be? Do you have one column
for values on the x axis and one column for values on the y? Why can't the
same number occur for both?

"Alfie" wrote:

I need a formula to assist me in counting instances where two values occur
together in a row through a specific range of cells. To put it simply, let's
say the numbers 1 through 20 are numbers that are returned from an array of
sensors tuned to those specific values, and I have 5 to 10 sensors returning
a stream of data. I have created a table with x and y axes both running 1 to
20, and want the intersecting cells to tell me how many times number from
axis y occurs with number from axis x. (I have built my table with
conditional formatting to gray out cells that intersect matching numbers, 1
and 1, etc.)

eg:
01 02 03 04 05 06 07 (...etc.)
01
02
03
04
05
06
(...etc.)

Been pouring through functions, examples, help files, and questions on here.
Have found nothing yet that is close to what I need. Anybody have an idea?

my original table's x-axis is the input values (sensor data), and the y-axis
is timing points, as follows:

Sensor #1 Sensor #2 Sensor #3 Sensor #4 Sensor #5
time point 1 22 23 25 26 37
time point 2 29 30 34 35 36
time point 3 10 19 21 22 31
time point 4 13 14 21 23 25
time point 5 07 09 13 20 26
time point 6 09 13 16 33 36
time point 7 05 12 16 19 20
time point 8 09 16 18 33 37
time point 9
time point 10

I have created another table on a new worksheet with both the x-axis and
y-axis being the standardized (rounded to whole) numbers coming in from
sensors taken from data in above example. What I want this new table to do
is count on the intersecting cells how many times two numbers occur from
different sensors on the same timing point (eg, how many times do 16 and 33
happen together, or how many time do 09 and 13 occur together, etc.) on a
count-chart or table as I had originally shown.

For the report that I need to create, which sensors are reporting what
values isn't critical, just what levels are ocurring together. I have been
trying and trying to get this to work, I can't seem to find a way to get
excel to count two values when they occur together.


"daddylonglegs" wrote:

Sounds like you need some sort of INDEX/MATCH formula but your explanation is
a little difficult to follow - where will the data be? Do you have one column
for values on the x axis and one column for values on the y? Why can't the
same number occur for both?

"Alfie" wrote:

I need a formula to assist me in counting instances where two values occur
together in a row through a specific range of cells. To put it simply, let's
say the numbers 1 through 20 are numbers that are returned from an array of
sensors tuned to those specific values, and I have 5 to 10 sensors returning
a stream of data. I have created a table with x and y axes both running 1 to
20, and want the intersecting cells to tell me how many times number from
axis y occurs with number from axis x. (I have built my table with
conditional formatting to gray out cells that intersect matching numbers, 1
and 1, etc.)

eg:
01 02 03 04 05 06 07 (...etc.)
01
02
03
04
05
06
(...etc.)

Been pouring through functions, examples, help files, and questions on here.
Have found nothing yet that is close to what I need. Anybody have an idea?