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

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