## Dynamic scoreboard

Here comes another post about the MMULT function, today I made a dynamic scoreboard.

There are five women competing and there are 8 rounds. Look what happens with the scoreboard as I type new values in round 3.

**Here is the array formula in cell C1:**

The formula works even if two or more people share the same total score.

Want to know more about the functions in the array formula above? INDEX, MATCH, COUNTIF, ROW, MMULT and LARGE

### How to enter an array formula

- Copy array formula above (CTRL + c)
- Select cell C1
- Click in formula bar
- Paste array formula to formula bar
- Press and hold CTRL + SHIFT simultaneoulsy
- Press Enter once
- Release all keys

If you did it right the formula in the formula bar now begins and ends with a curly brackets, like this ={array_formula}. Don´t type these characters yourself.

### Hiding zeros

I have hidden zeros in cell range D3:K7 by applying cell formatting, these are the steps:

- Select cell range D3:K7
- Press CTRL + 1
- Go to tab "Number"
- Click "Custom"
- Type: 0;-0;;@
- Click OK

Interested in learning more about array formulas? Join my Advanced excel course.

### Download excel *.xlsx file

### 7 Responses to “Dynamic scoreboard”

### 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.

**Contact Oscar**

You can contact me through this webpage

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)

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.

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

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

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!

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))

Can the above formula be used for 100 tables for 10 rounds? What would the formula to carry this out?

Instead of just 5 how would I do this for 50 people?