MIN/ MAX/ AVERAGE with array formula

Which ones is the champion?

🏇🏇🏇🏇🏇 Congrats Tina to be the best!

screen shot 2019-01-23 at 20.31.26

Let’s illustrate Excel array formula with a horse racing game as the following table.screen shot 2019-01-23 at 19.44.33

Data area in column Horse Name (table raw data) is named as horsename (F2:F16) and column Duration is named as duration (H2:H16).

From provided data, analysis result should be delivered as below. Data area in column Horse Name (table result) is named as nameresult (A2:A6).

screen shot 2019-01-23 at 19.57.38

Here, it can be seen that we need to figure out min, max and average duration time of each horse. In order to obtain it, the main idea is to collect 3 duration time values of each horse based on its name, then find min, max and average from those 3 values.

It means function MIN, MAX and AVERAGE would be used with condition(s) as known as IF. Moreover, each name in table result is compare to the list name (F2:F16) in table raw data, so array formula (or it is also called as matrix) should be used here too.

Let’s see MIN with IF in array formula.

screen shot 2019-01-23 at 19.50.04

Function IF is used to compare each row in nameresult to horsename to obtain 3 duration time values. As be seen from above, it will collect from the raw data 3 values whose have values in horsename match to A2 (Rocky). Then function MIN does it original role, which means returning min result of those 3 values.

Remember that A2 is compare to the whole list horsename, which means array formula; hence, in order to apply it, combination of Ctrl + Shift + Enter should be pressed instead of single Enter. That’s why there are { } in the formula.

Similar to MAX and AVERAGE

screen shot 2019-01-23 at 19.53.08

screen shot 2019-01-23 at 19.54.09

Now, let’s figure out who has the best duration (minimum value) for each category.

Here, INDEX and MATCH are used together to find out the best result in MIN category (B2:B6). Then simply drag the formula to column C and D for MAX and AVERAGE category.

screen shot 2019-01-23 at 19.55.02

It can be seen that Tina is the champion in 3 categories. Congrats Tina again!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s