Counting highest values within groups of four

P

PaladinWhite

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!
 
T

Teethless mama

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

ctrl+shift+enter, not just enter
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top