Min If Formula to Ignore 0

[bbalch]

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]

Is it only column E that will have blanks?

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

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

 

[CyrusTheVirus]

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
Bob		Round 1
Tom			Round 2
Kathy		Round 3
Sandra
Tony
Peter
Paul
Mary
Alessandra
Barbara
Jacob
Jasmine
Kyle
Kevin
Harry
Rebecca
Tiffany
Lisa
Larry

<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>
[TD="bgcolor: #CACACA, align: center"]1[/TD]
[TD="bgcolor: #C0C0C0"]Name[/TD]
[TD="bgcolor: #C0C0C0"]Age[/TD]
[TD="bgcolor: #C0C0C0"]Round 1[/TD]
[TD="bgcolor: #C0C0C0"]Round 2[/TD]
[TD="bgcolor: #C0C0C0"]Round 3[/TD]

[TD="bgcolor: #C0C0C0"]Round?[/TD]
[TD="bgcolor: #C0C0C0"]Minimum[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]

[TD="align: right"]18[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]31[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]

[TD="align: right"]11[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]81[/TD]

[TD="align: right"]4[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]

[TD="align: right"]14[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]46[/TD]

[TD="align: right"]43[/TD]

[TD="bgcolor: #CACACA, align: center"]5[/TD]

[TD="align: right"]16[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]12[/TD]

[TD="bgcolor: #C0C0C0"]Minimum[/TD]
[TD="bgcolor: #C0C0C0, align: right"]4[/TD]

[TD="bgcolor: #CACACA, align: center"]6[/TD]

[TD="align: right"]13[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]84[/TD]

[TD="bgcolor: #CACACA, align: center"]7[/TD]

[TD="align: right"]15[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]47[/TD]

[TD="bgcolor: #CACACA, align: center"]8[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]49[/TD]

[TD="bgcolor: #CACACA, align: center"]9[/TD]

[TD="align: right"]11[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]93[/TD]

[TD="bgcolor: #CACACA, align: center"]10[/TD]

[TD="align: right"]11[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]44[/TD]

[TD="bgcolor: #CACACA, align: center"]11[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]28[/TD]

[TD="bgcolor: #CACACA, align: center"]12[/TD]

[TD="align: right"]11[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]43[/TD]

[TD="bgcolor: #CACACA, align: center"]13[/TD]

[TD="align: right"]17[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]21[/TD]

[TD="bgcolor: #CACACA, align: center"]14[/TD]

[TD="align: right"]11[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]99[/TD]

[TD="bgcolor: #CACACA, align: center"]15[/TD]

[TD="align: right"]11[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]97[/TD]

[TD="bgcolor: #CACACA, align: center"]16[/TD]

[TD="align: right"]12[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]4[/TD]

[TD="bgcolor: #CACACA, align: center"]17[/TD]

[TD="align: right"]14[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]8[/TD]

[TD="bgcolor: #CACACA, align: center"]18[/TD]

[TD="align: right"]12[/TD]
[TD="align: right"]84[/TD]
[TD="align: right"]88[/TD]

[TD="bgcolor: #CACACA, align: center"]19[/TD]

[TD="align: right"]15[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]32[/TD]

[TD="bgcolor: #CACACA, align: center"]20[/TD]

[TD="align: right"]16[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]6[/TD]

</tbody>

Spreadsheet Formulas

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