Re: Count Intervals of 2 Numeric values in same Row and Return Count across Row
- From: "Sam via OfficeKB.com" <forum@xxxxxxxxxxxx>
- Date: Sat, 24 Sep 2005 03:10:39 GMT
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(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
--
Message posted via http://www.officekb.com
.
- Follow-Ups:
- References:
- Re: Count Intervals of 2 Numeric values in same Row and Return Count across Row
- From: Sam via OfficeKB.com
- Re: Count Intervals of 2 Numeric values in same Row and Return Count across Row
- From: Domenic
- Re: Count Intervals of 2 Numeric values in same Row and Return Count across Row
- Prev by Date: Re: problem with =isnumber(match(right(...
- Next by Date: Re: Display first, second, etc Nonblank Cells in a Range
- Previous by thread: Re: Count Intervals of 2 Numeric values in same Row and Return Count across Row
- Next by thread: Re: Count Intervals of 2 Numeric values in same Row and Return Count across Row
- Index(es):
Relevant Pages
|
