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


--
Message posted via http://www.officekb.com

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!

In article ,
"Sam via OfficeKB.com" wrote:

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

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


Domenic wrote:
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


--
Message posted via http://www.officekb.com

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.

In article ,
"Sam via OfficeKB.com" wrote:

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

Hi Domenic,

Domenic wrote:
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

Domenic wrote:
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


--
Message posted via http://www.officekb.com

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

Cheers!

In article ,
"Sam via OfficeKB.com" wrote:

Would very much appreciate sample file.

Cheers
Sam