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

Filter records that contain numeric values

Liam asks: Hello Oscar, What code is needed to cause cells in Columns F - I to fill with the […]

Search values distributed horizontally and return corresponding value

Question: Hi, The formula here works great but I can't figure out how to change it to work with data […]

Match two criteria and return multiple records

Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of […]

Find latest date based on a condition

Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]

Table of Contents Find closest value Find closest values Find closest values and return adjacent values Find closest value with […]

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Match two criteria and return multiple records

Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of […]

Find latest date based on a condition

Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]

How to create a list of random unique numbers

Question: How do I create a random list of unique numbers from say 1 to 10, without using VBA and […]

Extract unique distinct values from a multi-column cell range

Question: I have cell values spanning over several columns and I want to create a unique list from that range. […]

Calculate average of last 10 data with possible blank cells

Question: Answer: This array formula creates a dynamic range, filtering the 10 last data. Adjust cell ranges $A$1:$A$25 in formula below. […]

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Table of Contents Sort a column using array formula Two columns sorting by the second column Sort alphanumeric values I […]

Table of Contents Find closest value Find closest values Find closest values and return adjacent values Find closest value with […]

Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to […]

Filter records that contain numeric values

Liam asks: Hello Oscar, What code is needed to cause cells in Columns F - I to fill with the […]

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

The ROWS function allows you to calculate the number of rows in a cell range. The example above shows that cell […]

### 7 Responses to “Dynamic scoreboard”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

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?