Count Number of Maximums- Fantasy Football

[tjtemby]

I don't think this one is too crazy, but I am having trouble finding any info about this type of problem.

The goal is to count the number of times in a season that each team scored the highest points in a given week. The columns are labeled by weeks, the maximum per week is below. The end result should be "1" for each team with a red score (weekly high) and "0" for all others. Obviously I could use my fingers to count because we only have 16 weeks, but that would take all the un out of it.

Thanks and Go Lions

Team	1	2	3	4	5
Season 4 Swaggin	87.80	146.93	78.30	117.87	126.93
Stu Squad	82.17	104.00	134.57	104.40	91.00
Fantasy Enemy No.1	127.87	132.27	96.27	122.47	58.03
terminator	121.40	61.03	87.17	115.47	137.10
D-U-Lions	100.67	108.03	96.90	92.47	93.23
JC eXpress	73.50	76.77	103.77	103.07	117.70
Get Buck	100.23	74.37	79.97	114.53	103.17
The Brady Bunch	97.27	108.03	75.57	134.43	83.13
ShotThruTheHartline	97.80	107.60	80.90	78.00	90.93
Home by Midnight	85.00	67.30	84.93	104.03	111.70
Django	101.77	64.03	92.43	103.63	109.00
Inj Reserve Pt Tres	110.13	62.37	77.23	102.33	86.43
Weekly High	127.87	146.93	134.57	134.43	137.10

<tbody>
</tbody>

 

[Andrew Fergus]

Hello and welcome to MrExcel

Assuming you want to show the total in column R (allowing 16 columns for 16 weeks and 1 column for the team name) and assuming the max value is in the range B15:Q15, and assuming your data starts in row 2, the following array formula can be used in cell R2:

{=SUM((B2:Q2=$B$15:$Q$15)*(LEN(B2:Q2)>0))}

Enter the formula without the curly brackets, but before pressing enter, instead press Ctrl + Shift + Enter at the same time. This will convert the formula to an array formula - you will know you have done this correctly when the curly brackets automatically appear around the formula.

I trust this helps.

Andrew

 

[tjtemby]

That works. Array functions kinda throw me for a loop, but what this says to me is basically 'sum the occurances that a number in that row (2) is equal to a number is the same column below (B->Q), then the Length part is to not count weeks to be recorded'

{=SUM((B2:Q2=$B$15:$Q$15)*(LEN(B2:Q2)>0))}

Can you explain why it has to be multiplied by the length? Or does that mean something other than multiply in this function? I know that length gives the number of characters, and I think its is only 'real numbers' for a number cell. Or maybe I am even further away than that.

Thanks for your help.

 

[Andrew Fergus]

Thanks for the feedback. You are pretty much on to it.

The equation breaks into 3 parts, being:
(B2:Q2=$B$15:$Q$15) : this resolves as a series of TRUE and FALSE values depending on whether the value in B2 matches the value in B15, C2 matches C15 and so forth and you end up with an array of values like {FALSE, TRUE, FALSE etc}

The 2nd part is:
(LEN(B2:Q2)>0)
This is testing whether there is anything in the cells B2:Q2. The len returns the length of the value in the cell and we are testing to see if it is >0. If this test was excluded, then the blank cells will match the max value and return a false positive. Again this resolves as a series of booleans like {TRUE, TRUE, TRUE, TRUE, TRUE, FALSE, etc}

The third part adds together the product of the first {TRUE/FALSE} array and the second {TRUE/FALSE} array - so yes it is multiplying the TRUE and FALSE values against eachother. When the value TRUE is used in a mathematical function it returns the value 1, and the value FALSE returns the value 0. So the two arrays end up looking like this for row 2:

array 1 : FALSE, TRUE, FALSE, FALSE, FALSE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE
array 2 : TRUE, TRUE, TRUE, TRUE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE
which when pushed through the multiplication functions look like this:
=sum(1*0, 1*1, 0*1, 0*1, 1*0, 1*0, 1*0, 1*0, 1*0, 1*0, 1*0, 1*0, 1*0, 1*0, 1*0, 1*0)
which returns the sum of 1.

It's a handy way of adding together true instances of something, e.g.
{=sum(if(A1:A10=B1,1,0))}
can be written as:
{=sum(1*(A1:A10=B1))}
or another method of converting a TRUE/FALSE value to a number is to change it sign twice using a double negative like so:
{=sum(--(A1:A10=B1))}
These 3 methods all return the same value, which could just have easily been done using:
=COUNTIF(A1:A10,B1)
but COUNTIF only has one condition (although COUNTIFS in the latest version of Excel can handle multiple conditions) whereas other methods using array formulas or SUMPRODUCT formulas can return counts with multiple matches and thisngs like weighted averages etc.

As you will learn if you hang around MrExcel long enough, there is more than one way to skin a cat.....

Cheers
Andrew