Hi Domenic,
You understand perfectly. Your Formulas provided a brilliant working solution!
Thank you ever so much for all your great help.
Domenic wrote:
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
Domenic wrote:
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(Resu lts)-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
--
Message posted via
http://www.officekb.com