Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Explain return of #N/A
Hi Folks!
Trying to extract uniques from a list and make it dynamic. It works as long as there are at least 2 entries in the list. If there is only 1 entry in the list the formula errors but i don't understand why. The formula without the error trap: (array) =INDEX(List,MATCH(0,COUNTIF(A$1:A1,List),0)) List is a dynamic range name. With only a single entry in List and A1 being empty, this is how the formula evaluates: =INDEX({1},MATCH(0,0,0)) Match returns #N/A when it should return 1 since 0 matches 0 and it's in the first position of the lookup_array and the result of the entire formula shoud be 1 since 1 is in the first position of the Index array. Can anyone explain why this is not working? Thanks Biff |
#2
|
|||
|
|||
Biff,
Not sure why that isn't working but is it necessary to use this method? Could you use DataFilterAdvanced Filter? If your method is necessary please post details of the list dynamic formula and the type of data it contains. Thanks, Matt "Biff" wrote: Hi Folks! Trying to extract uniques from a list and make it dynamic. It works as long as there are at least 2 entries in the list. If there is only 1 entry in the list the formula errors but i don't understand why. The formula without the error trap: (array) =INDEX(List,MATCH(0,COUNTIF(A$1:A1,List),0)) List is a dynamic range name. With only a single entry in List and A1 being empty, this is how the formula evaluates: =INDEX({1},MATCH(0,0,0)) Match returns #N/A when it should return 1 since 0 matches 0 and it's in the first position of the lookup_array and the result of the entire formula shoud be 1 since 1 is in the first position of the Index array. Can anyone explain why this is not working? Thanks Biff |
#3
|
|||
|
|||
Hi!
Filters aren't dynamic! There's nothing wrong with the named range formula: =OFFSET(Sheet1!$A$1,,,COUNT(Sheet1!$A:$A)) The data is numeric. Biff "Matt Lunn" wrote in message ... Biff, Not sure why that isn't working but is it necessary to use this method? Could you use DataFilterAdvanced Filter? If your method is necessary please post details of the list dynamic formula and the type of data it contains. Thanks, Matt "Biff" wrote: Hi Folks! Trying to extract uniques from a list and make it dynamic. It works as long as there are at least 2 entries in the list. If there is only 1 entry in the list the formula errors but i don't understand why. The formula without the error trap: (array) =INDEX(List,MATCH(0,COUNTIF(A$1:A1,List),0)) List is a dynamic range name. With only a single entry in List and A1 being empty, this is how the formula evaluates: =INDEX({1},MATCH(0,0,0)) Match returns #N/A when it should return 1 since 0 matches 0 and it's in the first position of the lookup_array and the result of the entire formula shoud be 1 since 1 is in the first position of the Index array. Can anyone explain why this is not working? Thanks Biff |
#4
|
|||
|
|||
Biff,
Your named range formula may be wrong. At the moment it will start from A1 but create a formula with 0 rows and (count of Column A) amount of columns. This may be why this is evaluating to 0 in your formula? Try =OFFSET(Sheet1!$A$1,,COUNT(Sheet1!$A:$A),1) Thanks, Matt "Biff" wrote: Hi! Filters aren't dynamic! There's nothing wrong with the named range formula: =OFFSET(Sheet1!$A$1,,,COUNT(Sheet1!$A:$A)) The data is numeric. Biff "Matt Lunn" wrote in message ... Biff, Not sure why that isn't working but is it necessary to use this method? Could you use DataFilterAdvanced Filter? If your method is necessary please post details of the list dynamic formula and the type of data it contains. Thanks, Matt "Biff" wrote: Hi Folks! Trying to extract uniques from a list and make it dynamic. It works as long as there are at least 2 entries in the list. If there is only 1 entry in the list the formula errors but i don't understand why. The formula without the error trap: (array) =INDEX(List,MATCH(0,COUNTIF(A$1:A1,List),0)) List is a dynamic range name. With only a single entry in List and A1 being empty, this is how the formula evaluates: =INDEX({1},MATCH(0,0,0)) Match returns #N/A when it should return 1 since 0 matches 0 and it's in the first position of the lookup_array and the result of the entire formula shoud be 1 since 1 is in the first position of the Index array. Can anyone explain why this is not working? Thanks Biff |
#5
|
|||
|
|||
Hi!
The point of the formula is to not offset A1 by any rows. All I want it to do is return an array of values and as I have it written that's what it does. A1 is simply the start of the range. The end of the range depends upon the COUNT of column A. If there's nothing in the range it doesn't matter because there's nothing to extract! Just to reduce any confusion, the extraction formula is on a different sheet so the reference to A1 in that formula is not the same reference to A1 in the range name formula. Biff "Matt Lunn" wrote in message ... Biff, Your named range formula may be wrong. At the moment it will start from A1 but create a formula with 0 rows and (count of Column A) amount of columns. This may be why this is evaluating to 0 in your formula? Try =OFFSET(Sheet1!$A$1,,COUNT(Sheet1!$A:$A),1) Thanks, Matt "Biff" wrote: Hi! Filters aren't dynamic! There's nothing wrong with the named range formula: =OFFSET(Sheet1!$A$1,,,COUNT(Sheet1!$A:$A)) The data is numeric. Biff "Matt Lunn" wrote in message ... Biff, Not sure why that isn't working but is it necessary to use this method? Could you use DataFilterAdvanced Filter? If your method is necessary please post details of the list dynamic formula and the type of data it contains. Thanks, Matt "Biff" wrote: Hi Folks! Trying to extract uniques from a list and make it dynamic. It works as long as there are at least 2 entries in the list. If there is only 1 entry in the list the formula errors but i don't understand why. The formula without the error trap: (array) =INDEX(List,MATCH(0,COUNTIF(A$1:A1,List),0)) List is a dynamic range name. With only a single entry in List and A1 being empty, this is how the formula evaluates: =INDEX({1},MATCH(0,0,0)) Match returns #N/A when it should return 1 since 0 matches 0 and it's in the first position of the lookup_array and the result of the entire formula shoud be 1 since 1 is in the first position of the Index array. Can anyone explain why this is not working? Thanks Biff |
#6
|
|||
|
|||
Found a work around but would still like to know why this didn't work!
I put a blank, ="" , into cell A1 of the named range List. So the actual numeric entries will now have to start in cell A2. Also, since "" is a text value changed the named range formula using COUNTA instead of COUNT. Biff "Biff" wrote in message ... Hi! The point of the formula is to not offset A1 by any rows. All I want it to do is return an array of values and as I have it written that's what it does. A1 is simply the start of the range. The end of the range depends upon the COUNT of column A. If there's nothing in the range it doesn't matter because there's nothing to extract! Just to reduce any confusion, the extraction formula is on a different sheet so the reference to A1 in that formula is not the same reference to A1 in the range name formula. Biff "Matt Lunn" wrote in message ... Biff, Your named range formula may be wrong. At the moment it will start from A1 but create a formula with 0 rows and (count of Column A) amount of columns. This may be why this is evaluating to 0 in your formula? Try =OFFSET(Sheet1!$A$1,,COUNT(Sheet1!$A:$A),1) Thanks, Matt "Biff" wrote: Hi! Filters aren't dynamic! There's nothing wrong with the named range formula: =OFFSET(Sheet1!$A$1,,,COUNT(Sheet1!$A:$A)) The data is numeric. Biff "Matt Lunn" wrote in message ... Biff, Not sure why that isn't working but is it necessary to use this method? Could you use DataFilterAdvanced Filter? If your method is necessary please post details of the list dynamic formula and the type of data it contains. Thanks, Matt "Biff" wrote: Hi Folks! Trying to extract uniques from a list and make it dynamic. It works as long as there are at least 2 entries in the list. If there is only 1 entry in the list the formula errors but i don't understand why. The formula without the error trap: (array) =INDEX(List,MATCH(0,COUNTIF(A$1:A1,List),0)) List is a dynamic range name. With only a single entry in List and A1 being empty, this is how the formula evaluates: =INDEX({1},MATCH(0,0,0)) Match returns #N/A when it should return 1 since 0 matches 0 and it's in the first position of the lookup_array and the result of the entire formula shoud be 1 since 1 is in the first position of the Index array. Can anyone explain why this is not working? Thanks Biff |
#7
|
|||
|
|||
You may have moved on from this one, but in case it's still eating at your
brain ... I think it failed because the second argument in =MATCH(0,0,0) is looking for an array, not a single value. To test I entered just =MATCH(0,0,0) in a cell and it returned #NA, but then I entered =MATCH(0,{0},0) in another cell (I manually typed the {}) and it returned 1. HTH Rgds, ScottO "Biff" wrote in message ... | Found a work around but would still like to know why this didn't work! | | I put a blank, ="" , into cell A1 of the named range List. So the actual | numeric entries will now have to start in cell A2. | | Also, since "" is a text value changed the named range formula using COUNTA | instead of COUNT. | | Biff | | "Biff" wrote in message | ... | Hi! | | The point of the formula is to not offset A1 by any rows. All I want it to | do is return an array of values and as I have it written that's what it | does. A1 is simply the start of the range. The end of the range depends | upon the COUNT of column A. If there's nothing in the range it doesn't | matter because there's nothing to extract! | | Just to reduce any confusion, the extraction formula is on a different | sheet so the reference to A1 in that formula is not the same reference to | A1 in the range name formula. | | Biff | | "Matt Lunn" wrote in message | ... | Biff, | | Your named range formula may be wrong. At the moment it will start from | A1 | but create a formula with 0 rows and (count of Column A) amount of | columns. | This may be why this is evaluating to 0 in your formula? | | Try | | =OFFSET(Sheet1!$A$1,,COUNT(Sheet1!$A:$A),1) | | Thanks, | Matt | | "Biff" wrote: | | Hi! | | Filters aren't dynamic! | | There's nothing wrong with the named range formula: | | =OFFSET(Sheet1!$A$1,,,COUNT(Sheet1!$A:$A)) | | The data is numeric. | | Biff | | "Matt Lunn" wrote in message | ... | Biff, | | Not sure why that isn't working but is it necessary to use this | method? | | Could you use DataFilterAdvanced Filter? | | If your method is necessary please post details of the list dynamic | formula | and the type of data it contains. | | Thanks, | Matt | | "Biff" wrote: | | Hi Folks! | | Trying to extract uniques from a list and make it dynamic. It works | as | long | as there are at least 2 entries in the list. If there is only 1 entry | in | the | list the formula errors but i don't understand why. | | The formula without the error trap: (array) | | =INDEX(List,MATCH(0,COUNTIF(A$1:A1,List),0)) | | List is a dynamic range name. | | With only a single entry in List and A1 being empty, this is how the | formula | evaluates: | | =INDEX({1},MATCH(0,0,0)) | | Match returns #N/A when it should return 1 since 0 matches 0 and it's | in | the | first position of the lookup_array and the result of the entire | formula | shoud be 1 since 1 is in the first position of the Index array. | | Can anyone explain why this is not working? | | Thanks | | Biff | | | | | | | | | | | | | |
#8
|
|||
|
|||
in case it's still eating at your brain ...
Yes, it is. I'm still wanting an explanation, so I haven't moved on! Hmmm..... I think you're right. That would explain why it works if there are least 2 entries in the list. This only happens when I try making the whole thing dynamic. If the data is static it works just fine. But then that makes me ask why MATCH will accept (work with) a single element array constant {0}and not the single element array 0 that's generated by the COUNTIF? Hmmm.... I do have the work around but with this info maybe I can come up with a better work around. Thanks! Biff "ScottO" wrote in message ... You may have moved on from this one, but in case it's still eating at your brain ... I think it failed because the second argument in =MATCH(0,0,0) is looking for an array, not a single value. To test I entered just =MATCH(0,0,0) in a cell and it returned #NA, but then I entered =MATCH(0,{0},0) in another cell (I manually typed the {}) and it returned 1. HTH Rgds, ScottO "Biff" wrote in message ... | Found a work around but would still like to know why this didn't work! | | I put a blank, ="" , into cell A1 of the named range List. So the actual | numeric entries will now have to start in cell A2. | | Also, since "" is a text value changed the named range formula using COUNTA | instead of COUNT. | | Biff | | "Biff" wrote in message | ... | Hi! | | The point of the formula is to not offset A1 by any rows. All I want it to | do is return an array of values and as I have it written that's what it | does. A1 is simply the start of the range. The end of the range depends | upon the COUNT of column A. If there's nothing in the range it doesn't | matter because there's nothing to extract! | | Just to reduce any confusion, the extraction formula is on a different | sheet so the reference to A1 in that formula is not the same reference to | A1 in the range name formula. | | Biff | | "Matt Lunn" wrote in message | ... | Biff, | | Your named range formula may be wrong. At the moment it will start from | A1 | but create a formula with 0 rows and (count of Column A) amount of | columns. | This may be why this is evaluating to 0 in your formula? | | Try | | =OFFSET(Sheet1!$A$1,,COUNT(Sheet1!$A:$A),1) | | Thanks, | Matt | | "Biff" wrote: | | Hi! | | Filters aren't dynamic! | | There's nothing wrong with the named range formula: | | =OFFSET(Sheet1!$A$1,,,COUNT(Sheet1!$A:$A)) | | The data is numeric. | | Biff | | "Matt Lunn" wrote in message | ... | Biff, | | Not sure why that isn't working but is it necessary to use this | method? | | Could you use DataFilterAdvanced Filter? | | If your method is necessary please post details of the list dynamic | formula | and the type of data it contains. | | Thanks, | Matt | | "Biff" wrote: | | Hi Folks! | | Trying to extract uniques from a list and make it dynamic. It works | as | long | as there are at least 2 entries in the list. If there is only 1 entry | in | the | list the formula errors but i don't understand why. | | The formula without the error trap: (array) | | =INDEX(List,MATCH(0,COUNTIF(A$1:A1,List),0)) | | List is a dynamic range name. | | With only a single entry in List and A1 being empty, this is how the | formula | evaluates: | | =INDEX({1},MATCH(0,0,0)) | | Match returns #N/A when it should return 1 since 0 matches 0 and it's | in | the | first position of the lookup_array and the result of the entire | formula | shoud be 1 since 1 is in the first position of the Index array. | | Can anyone explain why this is not working? | | Thanks | | Biff | | | | | | | | | | | | | |
#9
|
|||
|
|||
Hi Biff!
I think Scott has the answer. However, if I try... =MATCH(0,A1,0) ....where A1 contains 0, the formula returns 1 not #N/A even though the second argument is a single value and not an array. So I don't quite understand why. In article , "Biff" wrote: Hi Folks! Trying to extract uniques from a list and make it dynamic. It works as long as there are at least 2 entries in the list. If there is only 1 entry in the list the formula errors but i don't understand why. The formula without the error trap: (array) =INDEX(List,MATCH(0,COUNTIF(A$1:A1,List),0)) List is a dynamic range name. With only a single entry in List and A1 being empty, this is how the formula evaluates: =INDEX({1},MATCH(0,0,0)) Match returns #N/A when it should return 1 since 0 matches 0 and it's in the first position of the lookup_array and the result of the entire formula shoud be 1 since 1 is in the first position of the Index array. Can anyone explain why this is not working? Thanks Biff |
#10
|
|||
|
|||
Thanks for taking a look, Domenic!
I don't like not understanding things. Biff "Domenic" wrote in message ... Hi Biff! I think Scott has the answer. However, if I try... =MATCH(0,A1,0) ...where A1 contains 0, the formula returns 1 not #N/A even though the second argument is a single value and not an array. So I don't quite understand why. In article , "Biff" wrote: Hi Folks! Trying to extract uniques from a list and make it dynamic. It works as long as there are at least 2 entries in the list. If there is only 1 entry in the list the formula errors but i don't understand why. The formula without the error trap: (array) =INDEX(List,MATCH(0,COUNTIF(A$1:A1,List),0)) List is a dynamic range name. With only a single entry in List and A1 being empty, this is how the formula evaluates: =INDEX({1},MATCH(0,0,0)) Match returns #N/A when it should return 1 since 0 matches 0 and it's in the first position of the lookup_array and the result of the entire formula shoud be 1 since 1 is in the first position of the Index array. Can anyone explain why this is not working? Thanks Biff |
#11
|
|||
|
|||
Got it!
=INDEX(List,MATCH(0,INDEX(COUNTIF(A$1:A1,List),,1) ,0)) Biff "Biff" wrote in message ... Thanks for taking a look, Domenic! I don't like not understanding things. Biff "Domenic" wrote in message ... Hi Biff! I think Scott has the answer. However, if I try... =MATCH(0,A1,0) ...where A1 contains 0, the formula returns 1 not #N/A even though the second argument is a single value and not an array. So I don't quite understand why. In article , "Biff" wrote: Hi Folks! Trying to extract uniques from a list and make it dynamic. It works as long as there are at least 2 entries in the list. If there is only 1 entry in the list the formula errors but i don't understand why. The formula without the error trap: (array) =INDEX(List,MATCH(0,COUNTIF(A$1:A1,List),0)) List is a dynamic range name. With only a single entry in List and A1 being empty, this is how the formula evaluates: =INDEX({1},MATCH(0,0,0)) Match returns #N/A when it should return 1 since 0 matches 0 and it's in the first position of the lookup_array and the result of the entire formula shoud be 1 since 1 is in the first position of the Index array. Can anyone explain why this is not working? Thanks Biff |
#12
|
|||
|
|||
In article ,
"Biff" wrote: Got it! =INDEX(List,MATCH(0,INDEX(COUNTIF(A$1:A1,List),,1) ,0)) Nice! So it looks like the reason COUNTIF(...) alone doesn't work is that it actually returns a single value, whereas INDEX(COUNTIF(...),,1) returns a single element array. So maybe the reason why... =MATCH(0,A1,0) ....works is that the reference to A1 is actually considered a single element array. Does it make sense? |
#13
|
|||
|
|||
I guess this is just one of those unexplainable nuances that Excel throws at
us every now and then! Biff "Domenic" wrote in message ... In article , "Biff" wrote: Got it! =INDEX(List,MATCH(0,INDEX(COUNTIF(A$1:A1,List),,1) ,0)) Nice! So it looks like the reason COUNTIF(...) alone doesn't work is that it actually returns a single value, whereas INDEX(COUNTIF(...),,1) returns a single element array. So maybe the reason why... =MATCH(0,A1,0) ...works is that the reference to A1 is actually considered a single element array. Does it make sense? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - return a picture or range rows as the result of a formula | Excel Worksheet Functions | |||
check if reference exists, then return its value or return 0 | Excel Worksheet Functions | |||
How do create a formula to evalute a # to return 1 of 4 conditions | Excel Worksheet Functions | |||
Using a Vlookup to return values in a data list? | Excel Worksheet Functions | |||
if the value of a cell in a range is not blank, then return the v. | Excel Worksheet Functions |