asked on

Background:

I tried this at home and at work, same result, this formula just shows up as text (2nd row)

=IF(F4="",0,1)

When typing the formula, Excel gives the hint info for the formula but it just comes back as text (2nd row)

The problem is with the file itself, which is exported from Qualtrics. All fields are text, even if blank. So if I COUNTA 200 rows, 100 of which appear blank, the result is "200"

If I insert a column in the middle of the file, it's still all text, the formula doesn't work

If I go beyond the last column of data, the formula works!

NOW FOR MY QUESTIONS.

1. How can I convert a column to be "clean", such that the formula works?

2. Why does it export this way (from Qualtrics)?

This would be easier than going to beyond the last column, copy back, etc.

I do have a workable solution, but would like to understand.

It's a big thing because I'm always downloading from Qualtrics and then doing data analysis in Excel.

I tried this at home and at work, same result, this formula just shows up as text (2nd row)

=IF(F4="",0,1)

When typing the formula, Excel gives the hint info for the formula but it just comes back as text (2nd row)

The problem is with the file itself, which is exported from Qualtrics. All fields are text, even if blank. So if I COUNTA 200 rows, 100 of which appear blank, the result is "200"

If I insert a column in the middle of the file, it's still all text, the formula doesn't work

If I go beyond the last column of data, the formula works!

NOW FOR MY QUESTIONS.

1. How can I convert a column to be "clean", such that the formula works?

2. Why does it export this way (from Qualtrics)?

This would be easier than going to beyond the last column, copy back, etc.

I do have a workable solution, but would like to understand.

It's a big thing because I'm always downloading from Qualtrics and then doing data analysis in Excel.

Microsoft ExcelMicrosoft Office