Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using booleans in sumproduct formulas to extract boolean range
I have a list of option with bolleans associated with them in range A1:B3:
Option 1 TRUE Option 2 TRUE Option 3 FALSE Then I have data list which uses these Options as validation items in range C1:C7: Option 1 Option 1 Option 1 Option 2 Option 2 Option 2 Option 3 I want to pull the range in C1:C7 into a SUMPRODUCT formula in the form {TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,} and then coverted to booleans {1,1,1,1,1,1,0,}. I know that if C1:C7 were excpressed as TRUE/FALSE I would do thei following below. SUMPRODUCT(--(C1:C7=TRUE)) How do I do the same considering the the values in C1:C7 are not expressed as TRUE/FALSE. I need to include a lookup extract the booleans. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using booleans in sumproduct formulas to extract boolean range
Since Option 1 and 2 are effectively the same thing here (both TRUE), then
why not test for condition to be not equal to Option 3? =SUMPRODUCT(--(C1:C7<"Option 3")) HTH, Elkar "ExcelMonkey" wrote: I have a list of option with bolleans associated with them in range A1:B3: Option 1 TRUE Option 2 TRUE Option 3 FALSE Then I have data list which uses these Options as validation items in range C1:C7: Option 1 Option 1 Option 1 Option 2 Option 2 Option 2 Option 3 I want to pull the range in C1:C7 into a SUMPRODUCT formula in the form {TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,} and then coverted to booleans {1,1,1,1,1,1,0,}. I know that if C1:C7 were excpressed as TRUE/FALSE I would do thei following below. SUMPRODUCT(--(C1:C7=TRUE)) How do I do the same considering the the values in C1:C7 are not expressed as TRUE/FALSE. I need to include a lookup extract the booleans. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using booleans in sumproduct formulas to extract boolean range
All three options can be TRUE or FALSE. So I cannot guarantee that doing
what you say will suffice when the user starts changing inputs. Thanks EM "Elkar" wrote: Since Option 1 and 2 are effectively the same thing here (both TRUE), then why not test for condition to be not equal to Option 3? =SUMPRODUCT(--(C1:C7<"Option 3")) HTH, Elkar "ExcelMonkey" wrote: I have a list of option with bolleans associated with them in range A1:B3: Option 1 TRUE Option 2 TRUE Option 3 FALSE Then I have data list which uses these Options as validation items in range C1:C7: Option 1 Option 1 Option 1 Option 2 Option 2 Option 2 Option 3 I want to pull the range in C1:C7 into a SUMPRODUCT formula in the form {TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,} and then coverted to booleans {1,1,1,1,1,1,0,}. I know that if C1:C7 were excpressed as TRUE/FALSE I would do thei following below. SUMPRODUCT(--(C1:C7=TRUE)) How do I do the same considering the the values in C1:C7 are not expressed as TRUE/FALSE. I need to include a lookup extract the booleans. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using booleans in sumproduct formulas to extract boolean range
Sorry, I misunderstood your post. Although, I'm still not sure what your
final goal here is. This isn't a SUMPRODUCT, but see if this helps any: =SUM(IF(C1:C7=$A$1,$B$1,0)+IF(C1:C7=$A$2,$B$2,0)+I F(C1:C7=$A$3,$B$3,0)) This is an array formula and should be entered with CTRL-SHIFT-ENTER instead of just ENTER. If done properly, the formula should be enclosed in { }. HTH, Elkar "ExcelMonkey" wrote: All three options can be TRUE or FALSE. So I cannot guarantee that doing what you say will suffice when the user starts changing inputs. Thanks EM "Elkar" wrote: Since Option 1 and 2 are effectively the same thing here (both TRUE), then why not test for condition to be not equal to Option 3? =SUMPRODUCT(--(C1:C7<"Option 3")) HTH, Elkar "ExcelMonkey" wrote: I have a list of option with bolleans associated with them in range A1:B3: Option 1 TRUE Option 2 TRUE Option 3 FALSE Then I have data list which uses these Options as validation items in range C1:C7: Option 1 Option 1 Option 1 Option 2 Option 2 Option 2 Option 3 I want to pull the range in C1:C7 into a SUMPRODUCT formula in the form {TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,} and then coverted to booleans {1,1,1,1,1,1,0,}. I know that if C1:C7 were excpressed as TRUE/FALSE I would do thei following below. SUMPRODUCT(--(C1:C7=TRUE)) How do I do the same considering the the values in C1:C7 are not expressed as TRUE/FALSE. I need to include a lookup extract the booleans. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using booleans in sumproduct formulas to extract boolean range
Yes this may work, but not necessarily in the capacity I want to use it in.
What I am doing is a query on a list of items in an excel spreadsheet. I am doing the query using a sumproduct function to act as a sumif with mulitple conditions. SUMPRODUCT(Condition1,Condition2,Condition3) Each condition is preceeded by the unary operator "--" SUMPRODUCT(--(D1:D20="Blue",E1:E20="North",F1:F20="Hot")) I now want to add a condition. Say G1:G20 have one of the following values: Option1, Option2 or Option3 which are in A1:A3. I want to be able to pull this array of values into the sumproudct and extract their TRUE/FALSE counterparts (B1:B3). This would be easier if G1:20 had the actual TRUE/FALSE values themselves in it. But it doesn't. So I need a way of doing a lookup SUMPRODUCT(--(D1:D20="Blue",E1:E20="North",F1:F20="Hot"), --SomeFunction(G1:G20 = TRUE)) Thanks EM "Elkar" wrote: Sorry, I misunderstood your post. Although, I'm still not sure what your final goal here is. This isn't a SUMPRODUCT, but see if this helps any: =SUM(IF(C1:C7=$A$1,$B$1,0)+IF(C1:C7=$A$2,$B$2,0)+I F(C1:C7=$A$3,$B$3,0)) This is an array formula and should be entered with CTRL-SHIFT-ENTER instead of just ENTER. If done properly, the formula should be enclosed in { }. HTH, Elkar "ExcelMonkey" wrote: All three options can be TRUE or FALSE. So I cannot guarantee that doing what you say will suffice when the user starts changing inputs. Thanks EM "Elkar" wrote: Since Option 1 and 2 are effectively the same thing here (both TRUE), then why not test for condition to be not equal to Option 3? =SUMPRODUCT(--(C1:C7<"Option 3")) HTH, Elkar "ExcelMonkey" wrote: I have a list of option with bolleans associated with them in range A1:B3: Option 1 TRUE Option 2 TRUE Option 3 FALSE Then I have data list which uses these Options as validation items in range C1:C7: Option 1 Option 1 Option 1 Option 2 Option 2 Option 2 Option 3 I want to pull the range in C1:C7 into a SUMPRODUCT formula in the form {TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,} and then coverted to booleans {1,1,1,1,1,1,0,}. I know that if C1:C7 were excpressed as TRUE/FALSE I would do thei following below. SUMPRODUCT(--(C1:C7=TRUE)) How do I do the same considering the the values in C1:C7 are not expressed as TRUE/FALSE. I need to include a lookup extract the booleans. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can SUMPRODUCT be used to extract varying data in a column? | Excel Worksheet Functions | |||
Extract last and next-to-last entries in a range | Excel Worksheet Functions | |||
Advanced Filtering Extract Range Missing,etc | Excel Worksheet Functions | |||
Boolean comparison: range vs. single cell | Excel Worksheet Functions | |||
Extract values from formulas | Excel Discussion (Misc queries) |