Forum Discussion
Data Import issue???
I am working on a drift formula for items being pushed by wind and waves on the water. I imported a text file of weather data, formatted all the data to numbers, and built an "if" formula to convert the wind direction into drift direction. Wind from 360 degrees (north) would cause an item to drift 180 degrees (south). The problem is the formula sees everything as false. Is this a formatting issue with the imported text? =IF(B28<180,B28+180,B28-180)
3 Replies
Hi there!
I agree, the problem is most likely due to the formatting of the data imported from the text file.
SOLUTION 1 - Fix the Formula:
Use the VALUE() function to convert text to number:
=IF(VALUE(B28)<180, VALUE(B28)+180, VALUE(B28)-180)
SOLUTION 2 - Fix the Data (Power Query):
If you frequently import such files, it's better to fix the formatting
from the start:- Data → Get Data → From Text/CSV
- Select your file
- In the Power Query Editor:
- Right-click on the column with wind degrees
- Change Type → Decimal Number (or Whole Number) - Close & Load
This way, the data will always be imported as numbers and your original
formula will work correctly.
Best,
Korina
In the world of Excel text is taken to be greater than any number. The simplest way of coercing text to numbers is to use it within an arithmetic expression so
driftDirection = MOD(windDirection + 180, 360)Here’s a drop-in replacement formula that will work no matter if the imported data is text or numbers, and will always return a valid drift direction (0–360°).
=MOD(VALUE(TRIM(B28))+180,360)
My answers are voluntary and without guarantee!
Hope this will help you.
