# Min If Formula to Ignore 0

#### bbalch

##### Board Regular
I have a table with golf scores for a junior golf tournament with the following columns: name (A2:A101), age (B2:B101), round 1 score (C2:C101), round 2 score (D2:D101), and round 3 score (E2:E101). All golfers participated in rounds 1 and 2 and half the field competed in round 3. The ages range from 10 – 18.
I want to a create formula to find the lowest score from all three rounds for golfers under the age of 13.

I’m attempting to use the array formula below but it returns a 0 since half the scores in column E are blank.

{=MIN(IF(B2:B101<13,(C2:E101)))}

Any suggestions on how to modify this formula to ignore 0’s or blanks? Or a different formula that will do the trick?

#### CyrusTheVirus

##### Well-known Member
Is it only column E that will have blanks?

If so, then perhaps the below formula would work for you?

Code:
``=MIN(MIN(IF(\$B\$2:\$B\$101<13,\$C\$2:\$C\$101)),MIN(IF(\$B\$2:\$B\$101<13,\$D\$2:\$D\$101)),MIN(IF(ISNUMBER(\$E\$2:\$E\$101),IF(\$B\$2:\$B\$101<13,\$E\$2:\$E\$101))))``

Last edited:

#### CyrusTheVirus

##### Well-known Member
Though, instead I'd probably setup the table with some helper cells like below.

The below formulas should account for any rounds with any person missing a round. Just change the range of the formulas.

And please ignore asterisks if they appear here, that is just a copy/paste issue, those cells are actually blank.

Sheet1

 A B C D E F G H 1 Name Age Round 1 Round 2 Round 3 Round? Minimum 2 Bob 18 18 32 2 Round 1 31 3 Tom 11 56 81 Round 2 4 4 Kathy 14 19 100 46 Round 3 43 5 Sandra 16 21 12 Minimum 4 6 Tony 13 46 84 7 Peter 15 18 76 47 8 Paul 10 65 90 49 9 Mary 11 51 93 10 Alessandra 11 31 45 44 11 Barbara 10 40 28 12 Jacob 11 56 32 43 13 Jasmine 17 49 65 21 14 Kyle 11 99 99 15 Kevin 11 44 7 97 16 Harry 12 73 4 17 Rebecca 14 76 11 8 18 Tiffany 12 84 88 19 Lisa 15 48 38 32 20 Larry 16 6 7 6

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 75px;"><col style="width: 58px;"><col style="width: 62px;"><col style="width: 62px;"><col style="width: 62px;"><col style="width: 40px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody>
</tbody>

 Cell Formula H2 {=MIN(IF(\$B\$2:\$B\$20<13,IF(ISNUMBER(\$C\$2:\$C\$20),\$C\$2:\$C\$20)))} H3 {=MIN(IF(\$B\$2:\$B\$20<13,IF(ISNUMBER(\$D\$2:\$D\$20),\$D\$2:\$D\$20)))} H4 {=MIN(IF(\$B\$2:\$B\$20<13,IF(ISNUMBER(\$E\$2:\$E\$20),\$E\$2:\$E\$20)))} H5 =MIN(H2:H4)

<tbody>
</tbody>
Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Last edited:

1,081,748
Messages
5,361,051
Members
400,610
Latest member
ebey

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...