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.
|