Difference between these two formulas?

[ker_01]

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

 

[Luke M]

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.

 

[ker_01]

Brilliant!

Many (many many) thanks

Keith

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!*

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

 

[ker_01]

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

Brilliant!

Many (many many) thanks

Keith

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!*

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

 

[Luke M]

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!*

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

Brilliant!

Many (many many) thanks

Keith

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!*


:


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

 

[ker_01]

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

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!*

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

Brilliant!

Many (many many) thanks

Keith

:

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!*


:


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