Counting highest values within groups of four
 

I have three columns of data:
* ColA contains arbitrary text identifier values, in groups of four.
* ColB contains arbitrary integer values, which can be negative, zero, or
positive.
* ColB contains TRUE and FALSE values. One and only one in every group of
four is TRUE - the other three are FALSE.

For instance:
Apple | -2 | FALSE
Apple | -1 | TRUE
Apple | 2 | FALSE
Apple | 1 | FALSE
Banana | 4 | TRUE
Banana | -2 | FALSE
Banana | -1 | FALSE
Banana | 1 | FALSE
Cherry | 4 | FALSE
Cherry | 0 | FALSE
Cherry | 2 | FALSE
Cherry | 2 | TRUE
€¦ | €¦ | €¦

I need to ask the question, "How many times does the highest ColB value in a
group of four correspond to TRUE in ColC?" In the example, this does not
occur in the first group; -1 is not the highest value among those four. It
does occur in the second group, where 4 corresponds to TRUE, but does not
occur in the third group. If this were my entire sample set, I would be
looking for a return value of 1.

Your help is greatly appreciated!

Counting highest values within groups of four
 

Try this:

=SUMPRODUCT(--(B1:B12=MAX(B1:B12)),--C1:C12)

--
Biff
Microsoft Excel MVP


"PaladinWhite" wrote in message
...
I have three columns of data:
* ColA contains arbitrary text identifier values, in groups of four.
* ColB contains arbitrary integer values, which can be negative, zero, or
positive.
* ColB contains TRUE and FALSE values. One and only one in every group of
four is TRUE - the other three are FALSE.

For instance:
Apple | -2 | FALSE
Apple | -1 | TRUE
Apple | 2 | FALSE
Apple | 1 | FALSE
Banana | 4 | TRUE
Banana | -2 | FALSE
Banana | -1 | FALSE
Banana | 1 | FALSE
Cherry | 4 | FALSE
Cherry | 0 | FALSE
Cherry | 2 | FALSE
Cherry | 2 | TRUE
. | . | .

I need to ask the question, "How many times does the highest ColB value in
a
group of four correspond to TRUE in ColC?" In the example, this does not
occur in the first group; -1 is not the highest value among those four. It
does occur in the second group, where 4 corresponds to TRUE, but does not
occur in the third group. If this were my entire sample set, I would be
looking for a return value of 1.

Your help is greatly appreciated!

Counting highest values within groups of four
 

P.S.

I'm assuming the TRUE or FALSE are logical values and not TEXT entries.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Try this:

=SUMPRODUCT(--(B1:B12=MAX(B1:B12)),--C1:C12)

--
Biff
Microsoft Excel MVP


"PaladinWhite" wrote in message
...
I have three columns of data:
* ColA contains arbitrary text identifier values, in groups of four.
* ColB contains arbitrary integer values, which can be negative, zero, or
positive.
* ColB contains TRUE and FALSE values. One and only one in every group of
four is TRUE - the other three are FALSE.

For instance:
Apple | -2 | FALSE
Apple | -1 | TRUE
Apple | 2 | FALSE
Apple | 1 | FALSE
Banana | 4 | TRUE
Banana | -2 | FALSE
Banana | -1 | FALSE
Banana | 1 | FALSE
Cherry | 4 | FALSE
Cherry | 0 | FALSE
Cherry | 2 | FALSE
Cherry | 2 | TRUE
. | . | .

I need to ask the question, "How many times does the highest ColB value
in a
group of four correspond to TRUE in ColC?" In the example, this does not
occur in the first group; -1 is not the highest value among those four.
It
does occur in the second group, where 4 corresponds to TRUE, but does not
occur in the third group. If this were my entire sample set, I would be
looking for a return value of 1.

Your help is greatly appreciated!

Counting highest values within groups of four
 

=SUM(IF((B1:B12=MAX((C1:C12=TRUE)*B1:B12))*(C1:C12 =TRUE),1))

ctrl+shift+enter, not just enter


"PaladinWhite" wrote:

I have three columns of data:
* ColA contains arbitrary text identifier values, in groups of four.
* ColB contains arbitrary integer values, which can be negative, zero, or
positive.
* ColB contains TRUE and FALSE values. One and only one in every group of
four is TRUE - the other three are FALSE.

For instance:
Apple | -2 | FALSE
Apple | -1 | TRUE
Apple | 2 | FALSE
Apple | 1 | FALSE
Banana | 4 | TRUE
Banana | -2 | FALSE
Banana | -1 | FALSE
Banana | 1 | FALSE
Cherry | 4 | FALSE
Cherry | 0 | FALSE
Cherry | 2 | FALSE
Cherry | 2 | TRUE
€¦ | €¦ | €¦

I need to ask the question, "How many times does the highest ColB value in a
group of four correspond to TRUE in ColC?" In the example, this does not
occur in the first group; -1 is not the highest value among those four. It
does occur in the second group, where 4 corresponds to TRUE, but does not
occur in the third group. If this were my entire sample set, I would be
looking for a return value of 1.

Your help is greatly appreciated!