Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#N/A
I am pulling some stat information off the web, into Excel spreadsheet. My
problem is there will be at times that a player will not play, and when this happens my formula keeps coming up with a #N/A error. My code is as follows: =IF([Game Stats-1.xls]Game-1'!$I$6="MY TEAM ROSTER",IF(ISERROR(VLOOKUP($A$2,[Game Stats-1.xls]Game-1'!$J$8:$J$25,1,FALSE)),"",(VLOOKUP($A$2,[Game Stats-1.xls]Game-1'!$J$8:$J$25,1,FALSE))/$A$2)) The {[Game Stats-1.xls]Game-1'!$I$6="MY TEAM ROSTER"} is for me to look for the team name that Im tracking. The {ISERROR(VLOOKUP($A$2,[Game Stats-1.xls]Game-1'!$J$8:$J$25,1,FALSE))} is for me to see if a particular player has played in this game. Im looking for a player number in roster column for that game, if player did not play, then put down a zero under that game, then see next line. The {(VLOOKUP($A$2,[Game Stats-1.xls]Game-1'!$J$8:$J$25,1,FALSE))/$A$2))} this part of the function will fill in the number for that particular category if player has played this game. $A$2 is the player number location on my spreadsheet. $J$8:$J$25 is the range of player numbers that have played in the game. Example: Say player number 23 played in the game, what I want my formula to do is take player number (from my spreadsheet) and divide by player number (from web information) to come up with one. Likewise if player number 23 did not play then a zero would be inputted. Any help in figuring out why I am getting a #N/A formula error every time a player HAS NOT played would be greatly appreciated. Steve |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#N/A
hy,
maybe you should try with "tolls" - "formula auditing" - "evaluate formula" this way excel will guide you untill process of calculating your result is done. For VLOOKUP error handling you can use ISNA function. "caldog" wrote in message ... I am pulling some stat information off the web, into Excel spreadsheet. My problem is there will be at times that a player will not play, and when this happens my formula keeps coming up with a #N/A error. My code is as follows: =IF([Game Stats-1.xls]Game-1'!$I$6="MY TEAM ROSTER",IF(ISERROR(VLOOKUP($A$2,[Game Stats-1.xls]Game-1'!$J$8:$J$25,1,FALSE)),"",(VLOOKUP($A$2,[Game Stats-1.xls]Game-1'!$J$8:$J$25,1,FALSE))/$A$2)) The {[Game Stats-1.xls]Game-1'!$I$6="MY TEAM ROSTER"} is for me to look for the team name that I'm tracking. The {ISERROR(VLOOKUP($A$2,[Game Stats-1.xls]Game-1'!$J$8:$J$25,1,FALSE))} is for me to see if a particular player has played in this game. I'm looking for a player number in roster column for that game, if player did not play, then put down a zero under that game, then see next line. The {(VLOOKUP($A$2,[Game Stats-1.xls]Game-1'!$J$8:$J$25,1,FALSE))/$A$2))} this part of the function will fill in the number for that particular category if player has played this game. $A$2 is the player number location on my spreadsheet. $J$8:$J$25 is the range of player numbers that have played in the game. Example: Say player number 23 played in the game, what I want my formula to do is take player number (from my spreadsheet) and divide by player number (from web information) to come up with one. Likewise if player number 23 did not play then a zero would be inputted. Any help in figuring out why I am getting a #N/A formula error every time a player HAS NOT played would be greatly appreciated. Steve |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#N/A
Sasa before posting to this fourm I tried that option. but it was no help at
all. I know what the problem is, Vlookup in not able to locate the number, but what I don't know is how to is create a formula to overcome this problem. Steve "Sasa Stankovic" wrote: hy, maybe you should try with "tolls" - "formula auditing" - "evaluate formula" this way excel will guide you untill process of calculating your result is done. For VLOOKUP error handling you can use ISNA function. "caldog" wrote in message ... I am pulling some stat information off the web, into Excel spreadsheet. My problem is there will be at times that a player will not play, and when this happens my formula keeps coming up with a #N/A error. My code is as follows: =IF([Game Stats-1.xls]Game-1'!$I$6="MY TEAM ROSTER",IF(ISERROR(VLOOKUP($A$2,[Game Stats-1.xls]Game-1'!$J$8:$J$25,1,FALSE)),"",(VLOOKUP($A$2,[Game Stats-1.xls]Game-1'!$J$8:$J$25,1,FALSE))/$A$2)) The {[Game Stats-1.xls]Game-1'!$I$6="MY TEAM ROSTER"} is for me to look for the team name that I'm tracking. The {ISERROR(VLOOKUP($A$2,[Game Stats-1.xls]Game-1'!$J$8:$J$25,1,FALSE))} is for me to see if a particular player has played in this game. I'm looking for a player number in roster column for that game, if player did not play, then put down a zero under that game, then see next line. The {(VLOOKUP($A$2,[Game Stats-1.xls]Game-1'!$J$8:$J$25,1,FALSE))/$A$2))} this part of the function will fill in the number for that particular category if player has played this game. $A$2 is the player number location on my spreadsheet. $J$8:$J$25 is the range of player numbers that have played in the game. Example: Say player number 23 played in the game, what I want my formula to do is take player number (from my spreadsheet) and divide by player number (from web information) to come up with one. Likewise if player number 23 did not play then a zero would be inputted. Any help in figuring out why I am getting a #N/A formula error every time a player HAS NOT played would be greatly appreciated. Steve |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#N/A
Instead of:
=IF([Game Stats-1.xls]Game-1'!$I$6="MY TEAM ROSTER",IF(ISERROR(VLOOKUP($A$2,[Game Stats-1.xls]Game-1'!$J$8:$J$25,1,FALSE)),"",(VLOOKUP($A$2,[Game Stats-1.xls]Game-1'!$J$8:$J$25,1,FALSE))/$A$2)) With Game Stats-1.xls simultaneously open, Try this simpler alternative in the destination sheet: =IF('[Game Stats-1.xls]Game-1'!$I$6="MY TEAM ROSTER",IF(ISNA(MATCH($A$2,'[Game Stats-1.xls]Game-1'!$J$8:$J$25,0)),"",1),"") Believe it achieves your underlying intents -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#N/A
Max
Thanks so much that works just perfectly. Steve "Max" wrote: Instead of: =IF([Game Stats-1.xls]Game-1'!$I$6="MY TEAM ROSTER",IF(ISERROR(VLOOKUP($A$2,[Game Stats-1.xls]Game-1'!$J$8:$J$25,1,FALSE)),"",(VLOOKUP($A$2,[Game Stats-1.xls]Game-1'!$J$8:$J$25,1,FALSE))/$A$2)) With Game Stats-1.xls simultaneously open, Try this simpler alternative in the destination sheet: =IF('[Game Stats-1.xls]Game-1'!$I$6="MY TEAM ROSTER",IF(ISNA(MATCH($A$2,'[Game Stats-1.xls]Game-1'!$J$8:$J$25,0)),"",1),"") Believe it achieves your underlying intents -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#N/A
Steve, you're welcome !
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "caldog" wrote in message ... Max Thanks so much that works just perfectly. Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|