Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"IN Operator" using Match function....
I am using the Match function to operate similar to a sql IN operator,
but I can't find anything that tells me what I am doing is okay. My test results have been correct, but I will be applying this formula to millions of dollars of calculations and don't want to get my figures off. What I am doing is basically this. I have column A containing usps two character mailing codes. Column B contains a number. I want to sum the values in column B where the value in column A is a state in the US thereby eliminating any rows where column A is a country code. I have a list of all the US state codes in column J. So I want to sum column B if the value in column A exists in the list in column J. Here is my simple example and formula. A B ... J WI 1 AL NZ 1 AK IL 1 AS CA 1 AZ EN 1 AR The forumula I am using is as follows =SUMPRODUCT(--(ISNUMBER(MATCH(A1:A5,J2:J60, 0))), B1:B5). It appears to be giving correct results (WI, IL and CA are being summed with england and new zealand left out), however, the MATCH function as documented is not supposed to take a range as the first parameter, only a value. Is this valid, or not, or is there a better way to do this? Thanks in advance for the help. Andy |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"IN Operator" using Match function....
Is this valid, or not
Yes it is. is there a better way to do this? That formula is about as efficient as it gets for the application. the MATCH function as documented is not supposed to take a range as the first parameter The help documentation is quite lacking (IMHO). You don't see anything in there about using the double unary minus with SUMPRODUCT, do you? -- Biff Microsoft Excel MVP wrote in message ... I am using the Match function to operate similar to a sql IN operator, but I can't find anything that tells me what I am doing is okay. My test results have been correct, but I will be applying this formula to millions of dollars of calculations and don't want to get my figures off. What I am doing is basically this. I have column A containing usps two character mailing codes. Column B contains a number. I want to sum the values in column B where the value in column A is a state in the US thereby eliminating any rows where column A is a country code. I have a list of all the US state codes in column J. So I want to sum column B if the value in column A exists in the list in column J. Here is my simple example and formula. A B ... J WI 1 AL NZ 1 AK IL 1 AS CA 1 AZ EN 1 AR The forumula I am using is as follows =SUMPRODUCT(--(ISNUMBER(MATCH(A1:A5,J2:J60, 0))), B1:B5). It appears to be giving correct results (WI, IL and CA are being summed with england and new zealand left out), however, the MATCH function as documented is not supposed to take a range as the first parameter, only a value. Is this valid, or not, or is there a better way to do this? Thanks in advance for the help. Andy |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"IN Operator" using Match function....
It appears to be giving correct results (WI, IL and CA are being
summed with england and new zealand left out), however, the MATCH function as documented is not supposed to take a range as the first parameter, only a value. I think that technically, it is not using a range, but rather an array. Arrays are barely documented at all. (as to usage) Many functions can handle arrays when entered properly, and none of them claim to do this in the help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text "comparison" operator for "contains" used in an "IF" Function | Excel Worksheet Functions | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Comparison Operator "" NOT "=" | Excel Discussion (Misc queries) | |||
Is it possible to apply IF's to "operator" result.. | Excel Discussion (Misc queries) | |||
program stuck at "Save Cancelled by Operator" | Excel Discussion (Misc queries) |