Don't use cells that have a 0 value

[Dave]

I have a formula:

=INDEX('Sign-Up Sheet'!B3:B124,MATCH(SMALL('Sign-Up
Sheet'!F3:F124,1),'Sign-Up Sheet'!F3:F124,0))

B3:B124 are names in my list of golfers
F3:F124 are handicaps of the golfers

Rows 72 through 124 are blank in column B because I might have more players.
Column F has the formula =SUM()/2 for each row, which is the handicap and
since there is no data in 72-124 it returns 0.00.

How can I stop the formula from using the cells that have 0.00?

Thanks,
Dave

 

[Mike H]

try

=INDEX('Sign-up sheet'!B3:B124,MATCH(MIN(IF('Sign-up
sheet'!F3:F124>0,'Sign-up sheet'!F3:F124)),'Sign-up sheet'!F3:F124))

Mike

 

[Dave]

Hi Mike,
Thanks for the response.
I tried this but it returns #N/A The formula is in a different work sheet
called "Pairings". This sheet pairs the High handicap players to the Low
handicap players.

Is there a way to send you the workbook so you can better understand my
problem?

 

[Mike H]

try this instead

=INDEX('Sign-up sheet'!B3:B124,MATCH(MIN(IF(('Sign-up
sheet'!F3:F124<>0),'Sign-up sheet'!F3:F124)),'Sign-up sheet'!F3:F124,0))

Its an arry so commit with CTRL+Shift+Enter NOT jusr enter. If you do it
correctly Excel will put curly brackets around it {} you can't type thses
yourself.

Mike

 

[Dave]

Mike,
This formula is in a merged cell and it it says I can't do that. The
original formula works in the merged cell though.
I un-merged the cell and tried it and i still get #N/A

 

[Mike H]

An array formula in a merged cell isn't an issue. you would get N/A for 1 of
2 reasons.

1. The numbers you are looking up aren't really numbers they may be text
that look like numbers. manually find the minimum number in your range
excluding zero and type this in another cell
=isnumber(Fxxx) where xxx is the row number. If it's a number the formula
will evaluate as TRUE. If it evaluates as FALSE then that's your problem.

2. You haven't entered the formula correctly. Paste the formula in a cell
and then press and hold CTRL+Shift and then tap Enter. As i said earlier if
you do it correctly then Excel wil put curly brackets around the formula {}.
You cannot type these yourself.

Mike

 

[Mike H]

Hi,

I'll correct myself before someone else does. You cannot enter an array
formula in a merged cell.

Strangely you can merge the cell after the formula has been entered and it
will still work.

 

[Dave]

Ok, that sort of works but the merged cell is an issue that I'm going to have
play with.
Thanks for your effort.