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