|
Don't use cells that have a 0 value
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 |
Don't use cells that have a 0 value
try
=INDEX('Sign-up sheet'!B3:B124,MATCH(MIN(IF('Sign-up sheet'!F3:F1240,'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 |
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:F1240,'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 |
Don't use cells that have a 0 value
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:F1240,'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 |
Don't use cells that have a 0 value
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:F1240,'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 |
Don't use cells that have a 0 value
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:F1240,'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 |
Don't use cells that have a 0 value
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:F1240,'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 |
Don't use cells that have a 0 value
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:F1240,'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 |
| All times are GMT +1. The time now is 10:36 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com