Don't use cells that have a 0 value

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" wrote:

> 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" wrote:
>
> > 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
> >

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" wrote:

> 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" wrote:
>
> > 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" wrote:
> >
> > > 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,
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" wrote:

> 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" wrote:
>
> > 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" wrote:
> >
> > > 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" wrote:
> > >
> > > > 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
> > > >

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

"Dave" wrote:

> 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" wrote:
>
> > 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" wrote:
> >
> > > 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" wrote:
> > >
> > > > 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" wrote:
> > > >
> > > > > 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
> > > > >

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.

"Mike H" wrote:

> 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
>
> "Dave" wrote:
>
> > 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" wrote:
> >
> > > 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" wrote:
> > >
> > > > 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" wrote:
> > > >
> > > > > 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" wrote:
> > > > >
> > > > > > 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
> > > > > >

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

"Mike H" wrote:

> 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.
>
> "Mike H" wrote:
>
> > 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
> >
> > "Dave" wrote:
> >
> > > 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" wrote:
> > >
> > > > 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" wrote:
> > > >
> > > > > 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" wrote:
> > > > >
> > > > > > 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" wrote:
> > > > > >
> > > > > > > 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
> > > > > > >