Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing #N/A occurrences
I have a vlookup that returns a logical 1 if a value is present. If it
is not present, the lookup fails and I get an #N/A back. So where I count up the 1s is fine, but I am having problems counting up the number of "#N/A" occurrences I have in that row. It returns "#N/A". So, I would like to either nest the Vlookup in an IF so that I get back a 0 when the lookup fails to find the referred search term, OR I would like to simply tally the number of "#N/A" occurrences in that row. These fail: =SUMIF(L2:AB2,(ISNA)) This works, but isn't really what I want... I think. =COUNTIF(L2:AB2,"#N/A") I'd rather correct the errant return from the vlookup to give a 0 when it fails to find the reference value. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing #N/A occurrences
Hi,
To eliminate NA# use this format for your vlookup =IF(ISNA(VLOOKUP(J1,A1:B11,2,FALSE)),0,VLOOKUP(J1, A1:B11,2,FALSE)) Mike "AtTheEndofMyRope" wrote: I have a vlookup that returns a logical 1 if a value is present. If it is not present, the lookup fails and I get an #N/A back. So where I count up the 1s is fine, but I am having problems counting up the number of "#N/A" occurrences I have in that row. It returns "#N/A". So, I would like to either nest the Vlookup in an IF so that I get back a 0 when the lookup fails to find the referred search term, OR I would like to simply tally the number of "#N/A" occurrences in that row. These fail: =SUMIF(L2:AB2,(ISNA)) This works, but isn't really what I want... I think. =COUNTIF(L2:AB2,"#N/A") I'd rather correct the errant return from the vlookup to give a 0 when it fails to find the reference value. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing #N/A occurrences
That works. Thanks. This is what I used:
=IF(ISNA(VLOOKUP(B33,Week17!K2:K17,1,FALSE)),0,1) Now, I need to also nest that in a loop to test for a blank cell,and return a blank for empty cells, which is different than a failed vlookup reference, so the blank cell check should be the first loop. The NA test finds out if it was a winner, as a lack of an #N/A on the vlookup represents a "hit" in the win column, Unless there was a bye or the game has not been played yet. That keeps games that have not yet been played from showing up as a loss. These are 17 weeks worth of 16 game per sheet weekly sheets. Some teams have a bye, which are listed at another place on the sheet, so I will eventually loop through that location as well, so that a bye does not show up as a win or loss. The result will be a sheet with all 32 teams on it that shows wins and losses compiled from the sheets which represent each week of gameplay. I am pretty close. I have found that summing zeros fails, and use countif to tally losses. As the weekly scores get filled in, those cells become no longer blank. There are only a few weeks that carry a bye. Almost done. Thanks for your help(s). On Sun, 30 Aug 2009 11:46:01 -0700, Mike H wrote: Hi, To eliminate NA# use this format for your vlookup =IF(ISNA(VLOOKUP(J1,A1:B11,2,FALSE)),0,VLOOKUP(J1 ,A1:B11,2,FALSE)) Mike "AtTheEndofMyRope" wrote: I have a vlookup that returns a logical 1 if a value is present. If it is not present, the lookup fails and I get an #N/A back. So where I count up the 1s is fine, but I am having problems counting up the number of "#N/A" occurrences I have in that row. It returns "#N/A". So, I would like to either nest the Vlookup in an IF so that I get back a 0 when the lookup fails to find the referred search term, OR I would like to simply tally the number of "#N/A" occurrences in that row. These fail: =SUMIF(L2:AB2,(ISNA)) This works, but isn't really what I want... I think. =COUNTIF(L2:AB2,"#N/A") I'd rather correct the errant return from the vlookup to give a 0 when it fails to find the reference value. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing #N/A occurrences
=IF(ISNA(VLOOKUP(B33,Week17!K2:K17,1,FALSE)),0, 1)
Sounds like you're doing something with the NFL season. So, a team can only appear once per week. This will do the same thing as the above formula: =COUNTIF(Week17!K2:K17,B33) -- Biff Microsoft Excel MVP "AtTheEndofMyRope" wrote in message ... That works. Thanks. This is what I used: =IF(ISNA(VLOOKUP(B33,Week17!K2:K17,1,FALSE)),0,1) Now, I need to also nest that in a loop to test for a blank cell,and return a blank for empty cells, which is different than a failed vlookup reference, so the blank cell check should be the first loop. The NA test finds out if it was a winner, as a lack of an #N/A on the vlookup represents a "hit" in the win column, Unless there was a bye or the game has not been played yet. That keeps games that have not yet been played from showing up as a loss. These are 17 weeks worth of 16 game per sheet weekly sheets. Some teams have a bye, which are listed at another place on the sheet, so I will eventually loop through that location as well, so that a bye does not show up as a win or loss. The result will be a sheet with all 32 teams on it that shows wins and losses compiled from the sheets which represent each week of gameplay. I am pretty close. I have found that summing zeros fails, and use countif to tally losses. As the weekly scores get filled in, those cells become no longer blank. There are only a few weeks that carry a bye. Almost done. Thanks for your help(s). On Sun, 30 Aug 2009 11:46:01 -0700, Mike H wrote: Hi, To eliminate NA# use this format for your vlookup =IF(ISNA(VLOOKUP(J1,A1:B11,2,FALSE)),0,VLOOKUP(J 1,A1:B11,2,FALSE)) Mike "AtTheEndofMyRope" wrote: I have a vlookup that returns a logical 1 if a value is present. If it is not present, the lookup fails and I get an #N/A back. So where I count up the 1s is fine, but I am having problems counting up the number of "#N/A" occurrences I have in that row. It returns "#N/A". So, I would like to either nest the Vlookup in an IF so that I get back a 0 when the lookup fails to find the referred search term, OR I would like to simply tally the number of "#N/A" occurrences in that row. These fail: =SUMIF(L2:AB2,(ISNA)) This works, but isn't really what I want... I think. =COUNTIF(L2:AB2,"#N/A") I'd rather correct the errant return from the vlookup to give a 0 when it fails to find the reference value. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing #N/A occurrences
On Sun, 30 Aug 2009 16:27:19 -0400, "T. Valko"
wrote: =IF(ISNA(VLOOKUP(B33,Week17!K2:K17,1,FALSE)),0,1 ) Sounds like you're doing something with the NFL season. So, a team can only appear once per week. This will do the same thing as the above formula: =COUNTIF(Week17!K2:K17,B33) B33 is on a local sheet, and does not get filled, it gets referenced to. It is a team city/state name. IF it fails to get found in the array, the "#N/A" return gets tallied as a loss. Otherwise a win is assumed. Actually, I am tallying a 17 week / sheet array. So, each week shows the team name of the winning team of a given game, based on the scores entered in that week's score keeping cells. Since the value is a text string, I count a 1 if it is present, as that would represent a win. I instead count the "#N/A" occurrences for losses, but assume that if "#N/A" is not found, it MUST be a win. The problem is when there is a bye, but that is a simple added loop which I should put in front of only those weeks where a bye applies. Otherwise, I only check for the N/A on the vlookup, and assume a loss if found, and a win if not. The teams sheet has an array that has these logical results in a 17 by 16 cell array. There is a wins column and a losses column that shows the counts of "1" and "0" in the array. So, IN the array, I should test for presence in the bye area on those 5? weeks. Otherwise, I only test for the N/A, and make assumptions based on that. I count 1s with =SUM and I count the zeros with =COUNTIF. Seems to show wins and losses pretty well. In the individual week sheets, where the game scores are stored, and the team name of the winning team gets filled in, I have an isblank loop to keep the field blank if there are no scores entered. That way, my teams sheet can check for a blank as well, return no value to the sum or countif cells, which should keep the wins and losses columns correct |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing #N/A occurrences
There are several things you can do. You can change your VLOOKUP
formulas to something like =IF(ISNA(VLOOKUP(....)),0,VLOOKUP(...)) The problem with this approach is that under most circumstances, the VLOOKUP is called twice, first within the ISNA function and if it doesn't return #N/A, it is called a second time. This duplication can cause performance issues. In Excel 2007, the IFERROR function was introduced, which allows you to specify a value to return if a formula returns an error. E.g, =IFERROR(VLOOKUP(...),0) If VLOOKUP is successful, its result is returned. If VLOOKUP returns an error, the result is 0. This function is available only in 2007 and later. It doesn't exist in 2003 and previous. The other approach is to leave your VLOOKUPs unchanged and write the SUM formula to ignore #N/A errors. E.g., if you have your VLOOKUPs in A1:A10, you can use the following array formula: =SUM(IF(ISNA(A1:A10),0,A1:A10)) If you just want to count the non-N/A cells, use either of the following array formulas. The produce the same result: =SUM(IF(ISNA(A1:A10),0,1)) =SUM(--NOT(ISNA(A1:A10))) Since these are Array Formulas, you *must* press CTRL SHIFT ENTER rather than just ENTER when you first enter the formula and whenever you edit it later. If you do this properly, Excel will display the formula in the Formula Bar enclosed in curly braces { }. (You do not type the curly braces - Excel includes them automatically.) The formula will not work properly if you do not use CTRL SHIFT ENTER. See http://www.cpearson.com/excel/ArrayFormulas.aspx for lots more information about array formulas. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sun, 30 Aug 2009 11:28:56 -0700, AtTheEndofMyRope wrote: I have a vlookup that returns a logical 1 if a value is present. If it is not present, the lookup fails and I get an #N/A back. So where I count up the 1s is fine, but I am having problems counting up the number of "#N/A" occurrences I have in that row. It returns "#N/A". So, I would like to either nest the Vlookup in an IF so that I get back a 0 when the lookup fails to find the referred search term, OR I would like to simply tally the number of "#N/A" occurrences in that row. These fail: =SUMIF(L2:AB2,(ISNA)) This works, but isn't really what I want... I think. =COUNTIF(L2:AB2,"#N/A") I'd rather correct the errant return from the vlookup to give a 0 when it fails to find the reference value. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count how many occurrences | Excel Discussion (Misc queries) | |||
Counting occurrences | Excel Discussion (Misc queries) | |||
Counting occurrences | Excel Discussion (Misc queries) | |||
sort 2 or more occurrences | Excel Worksheet Functions | |||
# of occurrences | Excel Worksheet Functions |