Count Intervals of 2 Numeric values in same Row and Return Count across Row

[Sam via OfficeKB.com]

Hi All,

Dynamic Named Range "Results" spans 5 Columns and many Rows (starts at Row 19)
.. Each cell
houses Numeric single-digit or double-digit values.

I require a Formula to calculate the INTERVALS (the number of Rows between
the LAST instance and the PREVIOUS instance in a column) between each
individual occurrence of any designated PAIR of Numeric values (single-digit
/ double-digit) in the same Row of the Named Range "Results" and return each
calculated INTERVAL result to a separate Column on the same Row of a New
Sheet - starting with the most recent ( the LAST) occurrence.

For instance, each time 80 and 87 appear together in the same Row, return the
INTERVAL by calculating the number of Rows between the LAST instance and the
PREVIOUS instance in a column - locate when both Numeric values LAST appeared
together and Count back to their PREVIOUS appearance together to get the
required Count; i.e. count from the Row ABOVE LAST appearance to the Row
BEFORE PREVIOUS appearance.

The results are returned to a chart / matrix layout: I have the criterion
vertically and horizontally and they are referenced using the horizontal and
vertical cell address that houses each criterion, and the results are
returned across the Row of the intercept of the vertical and horizontal
criterion. At some point both criterion values being referenced will be the
same, can the Formula return empty text "" when this occurs?

Example Chart / Matrix Layout:
Cell Ref. A2 and B1 criterion 80 and 80
Cell Ref. A3 and B1 criterion 81 and 80
Cell Ref. A4 and B1 criterion 82 and 80

Criteria B1 houses 80
A2 houses 80
A3 houses 81
A4 houses 82
A5 houses 83

Thanks
Sam

 

[Domenic]

Assuming that Sheet1 contains your 'Chart/Matrix Layout', try the
following...

1) Select B2 (your first intercept in your 'Chart/Matrix Layout')

2) Define the following references...

Insert > Name > Define

Name: Last

Refers to:

=MATCH(2,1/(MMULT(--(Results=Sheet1!$A2),TRANSPOSE(COLUMN(Results)^0))*MM
ULT(--(Results=Sheet1!B$1),TRANSPOSE(COLUMN(Results)^0))))

Click Add

Name: Previous

Refers to:

=LARGE(IF(MMULT(--(Results=Sheet1!$A2),TRANSPOSE(COLUMN(Results)^0))*MMUL
T(--(Results=Sheet1!B$1),TRANSPOSE(COLUMN(Results)^0)),ROW(Results)-MIN(R
OW(Results))+1),2)

Click Ok

3) Then enter the following formula in B2, copy down and across:

=IF($A2<>B$1,Last-Previous-1,"")

Note that you'll get an error value under the following circumstance...

#N/A - when a pair does not exist

#NUM! - when a second pair does not exist

You can always choose to use conditional formatting to hide these
errors, if you prefer. Post back if you need help.

Hope this helps!

 

[Sam via OfficeKB.com]

Hi Domenic,

Thank you for assistance.

I'm getting #N/A error in all cells except where both criterion are the same
numeric value and the Formula returns blank / empty cell as expected. I'm not
sure why the cells are returning the #N/A error: the criteria pair of values
will somtimes be consecutive but more often not.

Can you think of anything that may be causing the errors?

Cheers
Sam

Assuming that Sheet1 contains your 'Chart/Matrix Layout', try the
following...

1) Select B2 (your first intercept in your 'Chart/Matrix Layout')

2) Define the following references...

Insert > Name > Define

Name: Last

Refers to:

=MATCH(2,1/(MMULT(--(Results=Sheet1!$A2),TRANSPOSE(COLUMN(Results)^0))*MM
ULT(--(Results=Sheet1!B$1),TRANSPOSE(COLUMN(Results)^0))))

Click Add

Name: Previous

Refers to:

=LARGE(IF(MMULT(--(Results=Sheet1!$A2),TRANSPOSE(COLUMN(Results)^0))*MMUL
T(--(Results=Sheet1!B$1),TRANSPOSE(COLUMN(Results)^0)),ROW(Results)-MIN(R
OW(Results))+1),2)

Click Ok

3) Then enter the following formula in B2, copy down and across:

=IF($A2<>B$1,Last-Previous-1,"")

Note that you'll get an error value under the following circumstance...

#N/A - when a pair does not exist

#NUM! - when a second pair does not exist

You can always choose to use conditional formatting to hide these
errors, if you prefer. Post back if you need help.

Hope this helps!

Hi All,

[quoted text clipped - 36 lines]

Thanks
Sam

 

[Domenic]

A couple of questions...

1) Did you select B2 on your chart/matrix sheet before defining the
references?

2) Did you change the reference in both formulas from Sheet1 to the
actual name of the sheet containing your chart/matrix?

If these check out, I'd be happy to either look at your file or send you
a sample file.

 

[Sam via OfficeKB.com]

Hi Domenic,

A couple of questions...

1) Did you select B2 on your chart/matrix sheet before defining the
references?
Yes

2) Did you change the reference in both formulas from Sheet1 to the
actual name of the sheet containing your chart/matrix?
Yes

If these check out, I'd be happy to either look at your file or send you
a sample file.

Would very much appreciate sample file.

Cheers
Sam

A couple of questions...

1) Did you select B2 on your chart/matrix sheet before defining the
references?

2) Did you change the reference in both formulas from Sheet1 to the
actual name of the sheet containing your chart/matrix?

If these check out, I'd be happy to either look at your file or send you
a sample file.

Hi Domenic,

[quoted text clipped - 9 lines]

Cheers
Sam

 

[Domenic]

As per your request, I've just emailed you a sample file. Post back if
you need further help!

Cheers!

 

[Sam via OfficeKB.com]

Hi Domenic,

Thank you very much for sample file - I must have made an error when
transferring the Formulas. The #N/A errors are no more: I know get calculated
Intervals.

Unfortunately, I did not explain that I was looking for the calculated
Intervals of the two criterion to be displayed across their own individual
Row where they intersect - Interval calculated per your Formula but for each
occurrence of the paired criteria eg: 82 and 80 - Intervals displayed across
the entire Row where they intersect. I would have numerous criterion
vertically but only one horizontally. In the example below, your Formula
would correctly return blank for Intervals relating to criteria 80 80.
Criteria 81 80 should return various Intervals across that Row and the same
for the others such as 82 80 and 83 80.

Example Chart / Matrix Layout:
Cell Ref. A2 and B1 criterion 80 and 80
Cell Ref. A3 and B1 criterion 81 and 80
Cell Ref. A4 and B1 criterion 82 and 80

Criteria B1 houses 80
A2 houses 80
A3 houses 81
A4 houses 82 16 8 2 12 20 30 3 5 10 11 14
A5 houses 83

Cheers
Sam

 

[Domenic]

Okay, I think I understand. Let's see if I have this right...

1) Select B2

2) Define the following references...

Insert > Name > Define

Name: MMULT1

Refers to:

=MMULT(--(Results=Sheet1!$A2),TRANSPOSE(COLUMN(Results)^0))

Click Add

Name: MMULT2

Refers to:

=MMULT(--(Results=Sheet1!$B$1),TRANSPOSE(COLUMN(Results)^0))

Click Ok

3) Enter the following formula in B2, copy across and down:

=IF($A2<>$B$1,LARGE(IF((MMULT1)*(MMULT2),ROW(Results)-MIN(ROW(Results))+1
),COLUMNS($B2:B2))-LARGE(IF((MMULT1)*(MMULT2),ROW(Results)-MIN(ROW(Result
s))+1),COLUMNS($B2:B2)+1)-1,"")

....confirmed with CONTROL+SHIFT+ENTER.

4) Use conditional formatting to hide #NUM! error values that will
result when no other intervals exist. If you need help with this, post
back.

Note: In the interest of making the formula for B2 shorter and somewhat
more efficient, I tried to define references for both LARGE functions.
I was hoping to be able to do something like this...

=IF($A2<>$B$1,<FirstLargeReference>-<SecondLargeReference>-1,"")

But for some reason I get error messages for the second interval and
onwards. You may want to try this yourself, just in case it has
something to do with my version of Excel. If you need help with this,
post back.

Hope this helps!

 

[Sam via OfficeKB.com]

Hi Domenic,

You understand perfectly. Your Formulas provided a brilliant working solution!


Thank you ever so much for all your great help.

Note: In the interest of making the formula for B2 shorter and somewhat
more efficient, I tried to define references for both LARGE functions.
I was hoping to be able to do something like this...

=IF($A2<>$B$1,<FirstLargeReference>-<SecondLargeReference>-1,"")

But for some reason I get error messages for the second interval and
onwards. You may want to try this yourself, just in case it has
something to do with my version of Excel.

I defined both LARGE Function references as suggested using their respective
LARGE syntax in the Names Refers To Box and used the shorter Defined Names in
cell B2 - it worked whether they were array entered or not - calculating all
Intervals correctly.

Use conditional formatting to hide #NUM! error values that will
result when no other intervals exist. If you need help with this, post
back.

Would appreciate your assistance.


Cheers
Sam

Okay, I think I understand. Let's see if I have this right...

1) Select B2

2) Define the following references...

Insert > Name > Define

Name: MMULT1

Refers to:

=MMULT(--(Results=Sheet1!$A2),TRANSPOSE(COLUMN(Results)^0))

Click Add

Name: MMULT2

Refers to:

=MMULT(--(Results=Sheet1!$B$1),TRANSPOSE(COLUMN(Results)^0))

Click Ok

3) Enter the following formula in B2, copy across and down:

=IF($A2<>$B$1,LARGE(IF((MMULT1)*(MMULT2),ROW(Results)-MIN(ROW(Results))+1
),COLUMNS($B2:B2))-LARGE(IF((MMULT1)*(MMULT2),ROW(Results)-MIN(ROW(Result
s))+1),COLUMNS($B2:B2)+1)-1,"")

...confirmed with CONTROL+SHIFT+ENTER.

4) Use conditional formatting to hide #NUM! error values that will
result when no other intervals exist. If you need help with this, post
back.

Note: In the interest of making the formula for B2 shorter and somewhat
more efficient, I tried to define references for both LARGE functions.
I was hoping to be able to do something like this...

=IF($A2<>$B$1,<FirstLargeReference>-<SecondLargeReference>-1,"")

But for some reason I get error messages for the second interval and
onwards. You may want to try this yourself, just in case it has
something to do with my version of Excel. If you need help with this,
post back.

Hope this helps!

Hi Domenic,

[quoted text clipped - 25 lines]

Cheers
Sam

 

[Domenic]

Thank you ever so much for all your great help.

You're very welcome, Sam!

defined both LARGE Function references as suggested using their respective
LARGE syntax in the Names Refers To Box and used the shorter Defined Names in
cell B2 - it worked whether they were array entered or not - calculating all
Intervals correctly.

Great! I had a feeling it would work for you. I don't understand why
it doesn't work on my Mac version, though. There doesn't seem to be any
reason why it shouldn't. Oh well...

By the way, when defining named ranges or references, array formulas
don't need to be confirmed with CONTROL+SHIFT+ENTER.

Would appreciate your assistance.

1) Select B2

2) Format > Conditional Formatting > Formula Is

3) Enter the following formula:

=ISERR(B2)

4) Choose 'White' as your font colour

5) Click Ok

Hope this helps!

 

[Sam S via OfficeKB.com]

Hi Domenic,

Conditional Formatting help very much appreciated.

By the way, when defining named ranges or references, array formulas
don't need to be confirmed with CONTROL+SHIFT+ENTER.

Just to clarify: do you mean the shorter version of the Formula in B2 does
not have to be array entered?

 

[Domenic]

Just to clarify: do you mean the shorter version of the Formula in B2 does
not have to be array entered?

No, I meant if you enter an array formula via Insert > Name > Define >
it doesn't have to be array entered.

Cheers!

 

[Sam via OfficeKB.com]

Oh!

Cheers,
Sam

No, I meant if you enter an array formula via Insert > Name > Define >
it doesn't have to be array entered.

Cheers!