troubleshooting Question

Excel formulas not working, it's not reading it as a formula, only as text

Avatar of Wm Peck 1958
Wm Peck 1958Flag for United States of America asked on
Microsoft OfficeMicrosoft Excel
4 Comments1 Solution55 ViewsLast Modified:
Background:
I tried this at home and at work, same result, this formula just shows up as text (2nd row)
=IF(F4="",0,1)
Excel formula not working
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.
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros