Re: Array functions ARGHH!
From: Wazooli (Wazooli_at_discussions.microsoft.com)
Date: 03/16/05
- Next message: murat: "RE: Reduce columns and rows count?"
- Previous message: Bob Umlas: "Re: How do I sum numbers from cells that contain text"
- In reply to: Bob Phillips: "Re: Array functions ARGHH!"
- Next in thread: Bob Phillips: "Re: Array functions ARGHH!"
- Reply: Bob Phillips: "Re: Array functions ARGHH!"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 16 Mar 2005 11:43:01 -0800
Lol - haven't been called Waz since college. Anyway, why do the first
resolve to 0;0;0;...? This seems to me to be a mistake. If the query is
A=1, and B=0, there are 3 in the original data posted. Shouldn't the first
expression resolve to 0;0;1;1;1;0;0;0;0;...?
"Bob Phillips" wrote:
> Waz,
>
> I have replicated what you did and it still works for me. I get lots of 0 0
> entries, because A10-A20 all resolve to 0 0 (that is why my formula tested
> for them), but it works.
>
> The B part resolves to
> {TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;
> TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}
> for me, or
> {1;0;1;1;0;0;0;0;1;1;1;1;1;1;1;1;1;1;1;1}
> if I use the double unary. Thnis is in cell E2.
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Wazooli" <Wazooli@discussions.microsoft.com> wrote in message
> news:F95098D0-7A03-4F98-B991-F9781903654F@microsoft.com...
> > I used A1:B20 because JulieD suggested those. I merely filled in to
> complete
> > the ranges. If my table is D1:I6, with cell D1 being empty (the numbering
> > starts beneath and to the right), and I enter
> > =SUMPRODUCT(--($A$1:$A$20=$D2),--($B$1:$B$20=E$1), filldown and then to
> the
> > right, I get all zeroes. When I look at how Excel evaluates the formula,
> I
> > can verify that the '--' is working, but the values for the first part of
> the
> > expression resolve to '0;0;0;0;...' The second part is correct.
> >
> >
> >
> > "Bob Phillips" wrote:
> >
> > > It does!
> > >
> > > I just followed Domenic's instructions and it worked perfectly for me.
> > >
> > > Where does A20 and B20 com e into it in your reply?
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "Wazooli" <Wazooli@discussions.microsoft.com> wrote in message
> > > news:961E39CA-19A1-409C-9CFB-415813609DB2@microsoft.com...
> > > > Doesn't. When I look at how Excel evaluates the formula, it seems to
> have
> > > a
> > > > problem with $A$20 representing the first range. The sumproduct
> function
> > > > gives all zeroes. The range $B$20, on the other hand, presents no
> > > problem.
> > > >
> > > > wazooli
> > > >
> > > >
> > > > "Domenic" wrote:
> > > >
> > > > > Assumptions:
> > > > >
> > > > > 1) Sheet1 contains your source table
> > > > >
> > > > > 2) the first row in Sheet2 contains your numbers (0 through 4)
> starting
> > > > > at B1
> > > > >
> > > > > 3) the first column in Sheet2 contains your other set of numbers (0
> > > > > through 4) starting at A2
> > > > >
> > > > > Formula:
> > > > >
> > > > > Sheet2!B2, copied across and down:
> > > > >
> > > > > =SUMPRODUCT(--(Sheet1!$A$1:$A$9=$A2),--(Sheet1!$B$1:$B$9=B$1))
> > > > >
> > > > > Hope this helps!
> > > > >
> > > > > In article <Xns961B6DF502D66marcfleurysympaticoc@207.35.177.134>,
> > > > > Marc Fleury <marcfleury@sympatico.ca> wrote:
> > > > >
> > > > > > I'm pretty sure I need an array function for this, but nothing I
> do
> > > > > > works.
> > > > > >
> > > > > > My data is basically two colums:
> > > > > >
> > > > > > A B
> > > > > > 0 0
> > > > > > 0 2
> > > > > > 1 0
> > > > > > 1 0
> > > > > > 1 4
> > > > > > 2 1
> > > > > > 3 3
> > > > > > 3 3
> > > > > > 4 0
> > > > > >
> > > > > > etc
> > > > > >
> > > > > > Now, I need to create a table that counts how many instance of
> each
> > > pair
> > > > > > there are. Like so:
> > > > > >
> > > > > > 0 1 2 3 4
> > > > > > ___________
> > > > > > 0 |1 0 1 0 0
> > > > > > 1 |2 0 0 0 1
> > > > > > 2 |0 1 0 0 0
> > > > > > 3 |0 0 0 2 0
> > > > > > 4 |1 0 0 0 0
> > > > > >
> > > > > >
> > > > > > So the upper left cell counts how many times there is a 0 in
> column A
> > > > > > AND a 0 in colum B (once). The next cell to the right counts how
> many
> > > > > > times there is a 0 in column A and a 1 in colum B (zero times).
> Etc.
> > > > > >
> > > > > > The closest that I have come is
> > > > > >
> > > > > > {=SUM(IF(($A$1:$A$10=0)+($B$1:$B$10=0),1,0))}
> > > > > >
> > > > > > I actually have reference cells for the "0" that's being compared
> to,
> > > so
> > > > > > that I can use the same formula for every cell in the table, but I
> > > know
> > > > > > how to do that part.
> > > > > >
> > > > > > The problem with this formula is that it counts how many times
> there
> > > is
> > > > > > a zero in column A OR a zero in column B.
> > > > > >
> > > > > > HELP!
> > > > > >
> > > > > > --
> > > > > > Marc.
> > > > >
> > >
> > >
> > >
>
>
>
- Next message: murat: "RE: Reduce columns and rows count?"
- Previous message: Bob Umlas: "Re: How do I sum numbers from cells that contain text"
- In reply to: Bob Phillips: "Re: Array functions ARGHH!"
- Next in thread: Bob Phillips: "Re: Array functions ARGHH!"
- Reply: Bob Phillips: "Re: Array functions ARGHH!"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
