Frequency bug?

One Response to “Frequency bug?”

  1. Alex Groberman says:

    Hmm, seems like it's half Frequency's fault and half Mod's fault.

    =MOD(1.3,1) causes the issue, but =MOD(2.3,1) does not. Yet =MOD(1.3,1)=0.3 returns true. Very interesting catch!

Leave a Reply

How to add vba code to your comment:
[vb 1="vbnet" language=","]
your code
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org
Add picture link to comment.

How to upload a file
Upload file

Count overlapping days in multiple date ranges, part 2

Leave a Reply

How to add vba code to your comment:
[vb 1="vbnet" language=","]
your code
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org
Add picture link to comment.

How to upload a file
Upload file

Count overlapping days in multiple date ranges

Leave a Reply

How to add vba code to your comment:
[vb 1="vbnet" language=","]
your code
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org
Add picture link to comment.

How to upload a file
Upload file

Find missing dates in a set of date ranges

One Response to “Find missing dates in a set of date ranges”

  1. kidd says:

    Excellent. Thanks.

Leave a Reply

How to add vba code to your comment:
[vb 1="vbnet" language=","]
your code
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org
Add picture link to comment.

How to upload a file
Upload file

Dynamic scoreboard

5 Responses to “Dynamic scoreboard”

  1. GMF says:

    Oscar -

    Great concept - I'll be using this for sure. One question:

    You probably had a reason to have the COUNTIF statement and the final COLUMNS. But, by entering the entire non-total row as a single array value you can use the ability of INDEX to return an entire row or column using 0 as the parameter.

    =INDEX($C$11:$K$15, MATCH(LARGE(MMULT(IF($D$11:$K$15<>"", $D$11:$K$15,0), ROW(INDIRECT("1:8"))), ROWS($C$11:$C11)), MMULT(IF($D$11:$K$15<>"", $D$11:$K$15, 0), ROW(INDIRECT("1:8"))), 0), 0)

    • Oscar says:

      GMF

      Thank you, yes the COUNTIF function keeps track of names with the same total score.

      Sorry, wordpress eats html characters and I don´t know how to fix it. I edited your formula.

  2. GMF says:

    Sorry, the "not equal" values didn't get through the URL encoding. Let's try

    =INDEX($C$11:$K$15,MATCH(LARGE(MMULT(IF($D$11:$K$15<>"",$D$11:$K$15,0),ROW(INDIRECT("1:8"))),ROWS($C$11:$C11)),MMULT(IF($D$11:$K$15<>"",$D$11:$K$15,0),ROW(INDIRECT("1:8"))),0),0)
    
  3. GMF says:

    OK, I get the COUNTIF saves duplicates! One last time for the INDEX construction i talked about...

    =INDEX($C$11:$K$15,MATCH(LARGE(MMULT(IF($D$11:$K$15<>"",$D$11:$K$15,0),ROW(INDIRECT("1:3"))/ROW(INDIRECT("1:3"))),ROWS($C$11:$C11)),(COUNTIF($C$2:C2,$C$11:$C$15)=0)*MMULT(IF($D$11:$K$15<>"",$D$11:$K$15,0),ROW(INDIRECT("1:3"))/ROW(INDIRECT("1:3"))),0),0)
    

    I'll bet there's a cleaner way to generate an array of 1's for the MMULT function to cope with flexible numbers of columns for calculating the total, but I couldn't think of it right away!

  4. Bryan says:

    Here is what I did to create the one's vector

    =INDEX($C$11:$K$15,MATCH(LARGE(MMULT(IF($D$11:$K$15"",$D$11:$K$15,0),(1+0*ROW(OFFSET(INDIRECT("A1"),0,0,COUNTA($D$10:$K$10),1)))),ROWS($A$3:B3)),(COUNTIF($C$2:D2,$C$11:$C$15)=0)*MMULT(IF($D$11:$K$15"",$D$11:$K$15,0),(1+0*ROW(OFFSET(INDIRECT("A1"),0,0,COUNTA($D$10:$K$10),1)))),0),COLUMNS($A$3:B3))

Leave a Reply

How to add vba code to your comment:
[vb 1="vbnet" language=","]
your code
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org
Add picture link to comment.

How to upload a file
Upload file