Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Difference between these two formulas?
I have a raw data worksheet that has similar column headers (in row 1) identified by their first few letters; "B01_Yes", "B01_No", "B02_Yes", "B02_No", etc. Each row represents a single person's response to the survey. Their answer to any question (B01, B02, etc) will show up in those cells as a 1 under the corresponding answer column. I'm creating a summary sheet that will just have a single column for each question, and I want to populate the target cell with the actual answer given. (Column headers= "B01_", "B02_", etc) This formula correctly tells me if I the person answered just yes or no (and not both): =COUNT(AND(LEFT('Raw report'!1:1,4)=V$1,'Raw report'!2:2=1)) and I've confirmed that it returns a value of 1 additional info: 'Raw report'!1:1 are the headers, so taking the left 4 digits gives me the matches of B01_(Yes) and B01_(No) when V$1 = "B01_" However, when I wrap it with an IF statement, it returns an answer of FALSE, in this case, returning the "x" value/ =IF(AND(LEFT('Raw report'!$1:$1,4)=V$1,'Raw report'!2:2=1),"z","x") I'm thinking that COUNT ignores possible text or date values in my data row, and therefore can return a simple integer, but that the IF statement maybe can't get past non-numeric values when trying to calculate ['Raw report'!2:2=1] What would be a better way to evaluate whether the data row contains a 1, that wouldn't be affected by some cells having non-numeric values? Thank you!! Keith |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Difference between these two formulas?
I believe the issue is how the AND function handles arrays. It doesn't
properly multiply the two arrays to create a single array, and thus "clunks out". Workaround is to manually force the arrays to multiply: =IF((LEFT('Raw report'!$1:$1,4)=V$1)*('Raw report'!2:2=1),"z","x") Now the formula is forced to create a single array composed of 1's and 0's before moving on to the true_response and false_response portions of the IF function. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "ker_01" wrote: I have a raw data worksheet that has similar column headers (in row 1) identified by their first few letters; "B01_Yes", "B01_No", "B02_Yes", "B02_No", etc. Each row represents a single person's response to the survey. Their answer to any question (B01, B02, etc) will show up in those cells as a 1 under the corresponding answer column. I'm creating a summary sheet that will just have a single column for each question, and I want to populate the target cell with the actual answer given. (Column headers= "B01_", "B02_", etc) This formula correctly tells me if I the person answered just yes or no (and not both): =COUNT(AND(LEFT('Raw report'!1:1,4)=V$1,'Raw report'!2:2=1)) and I've confirmed that it returns a value of 1 additional info: 'Raw report'!1:1 are the headers, so taking the left 4 digits gives me the matches of B01_(Yes) and B01_(No) when V$1 = "B01_" However, when I wrap it with an IF statement, it returns an answer of FALSE, in this case, returning the "x" value/ =IF(AND(LEFT('Raw report'!$1:$1,4)=V$1,'Raw report'!2:2=1),"z","x") I'm thinking that COUNT ignores possible text or date values in my data row, and therefore can return a simple integer, but that the IF statement maybe can't get past non-numeric values when trying to calculate ['Raw report'!2:2=1] What would be a better way to evaluate whether the data row contains a 1, that wouldn't be affected by some cells having non-numeric values? Thank you!! Keith |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Difference between these two formulas?
Brilliant!
Many (many many) thanks Keith "Luke M" wrote: I believe the issue is how the AND function handles arrays. It doesn't properly multiply the two arrays to create a single array, and thus "clunks out". Workaround is to manually force the arrays to multiply: =IF((LEFT('Raw report'!$1:$1,4)=V$1)*('Raw report'!2:2=1),"z","x") Now the formula is forced to create a single array composed of 1's and 0's before moving on to the true_response and false_response portions of the IF function. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "ker_01" wrote: I have a raw data worksheet that has similar column headers (in row 1) identified by their first few letters; "B01_Yes", "B01_No", "B02_Yes", "B02_No", etc. Each row represents a single person's response to the survey. Their answer to any question (B01, B02, etc) will show up in those cells as a 1 under the corresponding answer column. I'm creating a summary sheet that will just have a single column for each question, and I want to populate the target cell with the actual answer given. (Column headers= "B01_", "B02_", etc) This formula correctly tells me if I the person answered just yes or no (and not both): =COUNT(AND(LEFT('Raw report'!1:1,4)=V$1,'Raw report'!2:2=1)) and I've confirmed that it returns a value of 1 additional info: 'Raw report'!1:1 are the headers, so taking the left 4 digits gives me the matches of B01_(Yes) and B01_(No) when V$1 = "B01_" However, when I wrap it with an IF statement, it returns an answer of FALSE, in this case, returning the "x" value/ =IF(AND(LEFT('Raw report'!$1:$1,4)=V$1,'Raw report'!2:2=1),"z","x") I'm thinking that COUNT ignores possible text or date values in my data row, and therefore can return a simple integer, but that the IF statement maybe can't get past non-numeric values when trying to calculate ['Raw report'!2:2=1] What would be a better way to evaluate whether the data row contains a 1, that wouldn't be affected by some cells having non-numeric values? Thank you!! Keith |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Difference between these two formulas?
Since it is related, I'll post my followup here (hoping that it is noticed
even though I've closed the thread). It looks like I spoke too soon; after additional testing, I narrowed the formula down to: =IF((LEFT('Raw report'!$A1:$IH1,4)=V$1),"z","x") which eliminates the COUNT and AND statements. It also eliminates the 'unlimited' range of the full row (1:1) in case that was a problem. It still returns "x" (False) even though I have confirmed that there is one (and only one) match using V4: =Left('Raw report'!P1,4) = gives the expected value V5: =V4=V1 = evaluates to 1 (true) Autofilling the formula left and right shows that all other columns return 0 (false) Once I get this working, it will be embedded in a statement that includes a sumproduct (part of an outer IF statement) so I don't think I can use an array formula, although it could be that my syntax attempts are just wrong Any additional assistance would be greatly appreciated! Best, Keith "ker_01" wrote: Brilliant! Many (many many) thanks Keith "Luke M" wrote: I believe the issue is how the AND function handles arrays. It doesn't properly multiply the two arrays to create a single array, and thus "clunks out". Workaround is to manually force the arrays to multiply: =IF((LEFT('Raw report'!$1:$1,4)=V$1)*('Raw report'!2:2=1),"z","x") Now the formula is forced to create a single array composed of 1's and 0's before moving on to the true_response and false_response portions of the IF function. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "ker_01" wrote: I have a raw data worksheet that has similar column headers (in row 1) identified by their first few letters; "B01_Yes", "B01_No", "B02_Yes", "B02_No", etc. Each row represents a single person's response to the survey. Their answer to any question (B01, B02, etc) will show up in those cells as a 1 under the corresponding answer column. I'm creating a summary sheet that will just have a single column for each question, and I want to populate the target cell with the actual answer given. (Column headers= "B01_", "B02_", etc) This formula correctly tells me if I the person answered just yes or no (and not both): =COUNT(AND(LEFT('Raw report'!1:1,4)=V$1,'Raw report'!2:2=1)) and I've confirmed that it returns a value of 1 additional info: 'Raw report'!1:1 are the headers, so taking the left 4 digits gives me the matches of B01_(Yes) and B01_(No) when V$1 = "B01_" However, when I wrap it with an IF statement, it returns an answer of FALSE, in this case, returning the "x" value/ =IF(AND(LEFT('Raw report'!$1:$1,4)=V$1,'Raw report'!2:2=1),"z","x") I'm thinking that COUNT ignores possible text or date values in my data row, and therefore can return a simple integer, but that the IF statement maybe can't get past non-numeric values when trying to calculate ['Raw report'!2:2=1] What would be a better way to evaluate whether the data row contains a 1, that wouldn't be affected by some cells having non-numeric values? Thank you!! Keith |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Difference between these two formulas?
Your formula is...incomplete. The logical test returns an array, but your
results are both singular answers. As such, XL is only taking the first value from the array to determine which path to take. If, your test is "If V1 = any of the Left(a1:IH1,4), then spit out "z", else "x"", then the formula would be: =IF(SUMPRODUCT(--(LEFT('Raw report'!$A1:$IH1,4)=V$1)),"z","x") The double elipse "--" transforms true/false array into 1's and 0's, and the SUMPRODUCT function reduces the array to a singular value. Note that this formula does not need to be confirmed as an array function. If this formula is actually nested in something else, it might be easier to design the formula if we knew the entire goal. Hope it helps! -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "ker_01" wrote: Since it is related, I'll post my followup here (hoping that it is noticed even though I've closed the thread). It looks like I spoke too soon; after additional testing, I narrowed the formula down to: =IF((LEFT('Raw report'!$A1:$IH1,4)=V$1),"z","x") which eliminates the COUNT and AND statements. It also eliminates the 'unlimited' range of the full row (1:1) in case that was a problem. It still returns "x" (False) even though I have confirmed that there is one (and only one) match using V4: =Left('Raw report'!P1,4) = gives the expected value V5: =V4=V1 = evaluates to 1 (true) Autofilling the formula left and right shows that all other columns return 0 (false) Once I get this working, it will be embedded in a statement that includes a sumproduct (part of an outer IF statement) so I don't think I can use an array formula, although it could be that my syntax attempts are just wrong Any additional assistance would be greatly appreciated! Best, Keith "ker_01" wrote: Brilliant! Many (many many) thanks Keith "Luke M" wrote: I believe the issue is how the AND function handles arrays. It doesn't properly multiply the two arrays to create a single array, and thus "clunks out". Workaround is to manually force the arrays to multiply: =IF((LEFT('Raw report'!$1:$1,4)=V$1)*('Raw report'!2:2=1),"z","x") Now the formula is forced to create a single array composed of 1's and 0's before moving on to the true_response and false_response portions of the IF function. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "ker_01" wrote: I have a raw data worksheet that has similar column headers (in row 1) identified by their first few letters; "B01_Yes", "B01_No", "B02_Yes", "B02_No", etc. Each row represents a single person's response to the survey. Their answer to any question (B01, B02, etc) will show up in those cells as a 1 under the corresponding answer column. I'm creating a summary sheet that will just have a single column for each question, and I want to populate the target cell with the actual answer given. (Column headers= "B01_", "B02_", etc) This formula correctly tells me if I the person answered just yes or no (and not both): =COUNT(AND(LEFT('Raw report'!1:1,4)=V$1,'Raw report'!2:2=1)) and I've confirmed that it returns a value of 1 additional info: 'Raw report'!1:1 are the headers, so taking the left 4 digits gives me the matches of B01_(Yes) and B01_(No) when V$1 = "B01_" However, when I wrap it with an IF statement, it returns an answer of FALSE, in this case, returning the "x" value/ =IF(AND(LEFT('Raw report'!$1:$1,4)=V$1,'Raw report'!2:2=1),"z","x") I'm thinking that COUNT ignores possible text or date values in my data row, and therefore can return a simple integer, but that the IF statement maybe can't get past non-numeric values when trying to calculate ['Raw report'!2:2=1] What would be a better way to evaluate whether the data row contains a 1, that wouldn't be affected by some cells having non-numeric values? Thank you!! Keith |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Difference between these two formulas?
Luke- thank you for your continued time and assistance.
The overall scenario: I have a data dump of Word formfields that were used in a questionaire. Some questions requested text responses, but many were Yes/No/NA checkboxes, so the data file has three columns per each of those questions, and I am trying to collapse it to one column per question that shows the specific answer selected. The question types are all intermixed throughout the survey. Raw data file: A B C D E F Header Row: Q01_Yes Q01_No Q01_NA Q02_Text Q03_Yes Q03_No etc Data: 1 0 0 "Hello" 0 1 So in my new sheet, I have the following headers, and my desired return value from the formula: A B C D Header Row: Q01_ Q02_Text Q03_ Q04_ etc Data: Yes "Hello" No Yes So far, here's my full formula: =IF(SUMPRODUCT((LEFT('Raw report'!$1:$1,4)=U$1)*1,('Raw report'!2:2))1,"MULT",IF(SUMPRODUCT((LEFT('Raw report'!$1:$1,4)=U$1)*1,('Raw report'!2:2))=1,RIGHT('Raw report'!$1:$1,LEN('Raw report'!$1:$1)-4),"")) The first IF evaluates to make sure the respondent didn't check multiple checkboxes for the question ("MULT"); the answers are mutually exclusive options ("is your site fully compliant with HR policy 1047"). Those will require manual followup with those branches. If it calculates that there is only a single matching response, then I want to return the "Yes", "No", or "NA" from the header row, which is row 1 (I just subtract 4 from the left side to get rid of the question ID). That's where I'm stuck- I'm still unable to grab the single cell value from column A that matches the criteria of (a) prefix matches the target question, and (b) has a value of 1 in the target row. Target row in this formula is row 2, my first row of data (I'll autofill it for all rows once it is working. Many thanks, Keith "Luke M" wrote: Your formula is...incomplete. The logical test returns an array, but your results are both singular answers. As such, XL is only taking the first value from the array to determine which path to take. If, your test is "If V1 = any of the Left(a1:IH1,4), then spit out "z", else "x"", then the formula would be: =IF(SUMPRODUCT(--(LEFT('Raw report'!$A1:$IH1,4)=V$1)),"z","x") The double elipse "--" transforms true/false array into 1's and 0's, and the SUMPRODUCT function reduces the array to a singular value. Note that this formula does not need to be confirmed as an array function. If this formula is actually nested in something else, it might be easier to design the formula if we knew the entire goal. Hope it helps! -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "ker_01" wrote: Since it is related, I'll post my followup here (hoping that it is noticed even though I've closed the thread). It looks like I spoke too soon; after additional testing, I narrowed the formula down to: =IF((LEFT('Raw report'!$A1:$IH1,4)=V$1),"z","x") which eliminates the COUNT and AND statements. It also eliminates the 'unlimited' range of the full row (1:1) in case that was a problem. It still returns "x" (False) even though I have confirmed that there is one (and only one) match using V4: =Left('Raw report'!P1,4) = gives the expected value V5: =V4=V1 = evaluates to 1 (true) Autofilling the formula left and right shows that all other columns return 0 (false) Once I get this working, it will be embedded in a statement that includes a sumproduct (part of an outer IF statement) so I don't think I can use an array formula, although it could be that my syntax attempts are just wrong Any additional assistance would be greatly appreciated! Best, Keith "ker_01" wrote: Brilliant! Many (many many) thanks Keith "Luke M" wrote: I believe the issue is how the AND function handles arrays. It doesn't properly multiply the two arrays to create a single array, and thus "clunks out". Workaround is to manually force the arrays to multiply: =IF((LEFT('Raw report'!$1:$1,4)=V$1)*('Raw report'!2:2=1),"z","x") Now the formula is forced to create a single array composed of 1's and 0's before moving on to the true_response and false_response portions of the IF function. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "ker_01" wrote: I have a raw data worksheet that has similar column headers (in row 1) identified by their first few letters; "B01_Yes", "B01_No", "B02_Yes", "B02_No", etc. Each row represents a single person's response to the survey. Their answer to any question (B01, B02, etc) will show up in those cells as a 1 under the corresponding answer column. I'm creating a summary sheet that will just have a single column for each question, and I want to populate the target cell with the actual answer given. (Column headers= "B01_", "B02_", etc) This formula correctly tells me if I the person answered just yes or no (and not both): =COUNT(AND(LEFT('Raw report'!1:1,4)=V$1,'Raw report'!2:2=1)) and I've confirmed that it returns a value of 1 additional info: 'Raw report'!1:1 are the headers, so taking the left 4 digits gives me the matches of B01_(Yes) and B01_(No) when V$1 = "B01_" However, when I wrap it with an IF statement, it returns an answer of FALSE, in this case, returning the "x" value/ =IF(AND(LEFT('Raw report'!$1:$1,4)=V$1,'Raw report'!2:2=1),"z","x") I'm thinking that COUNT ignores possible text or date values in my data row, and therefore can return a simple integer, but that the IF statement maybe can't get past non-numeric values when trying to calculate ['Raw report'!2:2=1] What would be a better way to evaluate whether the data row contains a 1, that wouldn't be affected by some cells having non-numeric values? Thank you!! Keith |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
subtract the time difference from another time difference | Excel Discussion (Misc queries) | |||
difference between the 2 | Excel Worksheet Functions | |||
Could someone tell me the difference in these formulas? | Excel Worksheet Functions | |||
charting a difference of 2 columns' w/o adding a difference column | Charts and Charting in Excel | |||
up down bar value (difference) | Charts and Charting in Excel |