Today I ran in an interesting issue where I imported a CSV and the Price column was loaded as text instead of currency. Because of that, it was not seen as a number.

But first, I wanted to add a column where I would show the number for Purchases and another column for Sales. So the formula would look something like this:

=IF(E2="Purchase";I2;0)

And similarly for sales:

=IF(E2="Sales";I2;0)

As you can see, my issue was that the price was in a single column so I could not compute a total for purchases and a column for sales.

The formula would not take, however. LibreOffice Calc would show me the formula instead of the result. What gives?

The fact is that cells of the columns I just inserted were all marked as "Text". That type means that it's not a formula. The data has to be managed as text no matter what.

So I selected the column and marked them as Number / Currency. That did not fix the existing formula, so I thought it did not work. Tried a few times... Nope... Still the showing the formula instead of the $$$.

The fact is that I had to delete it and type anew. The existing "formula" was just viewed asa string of text. Editing it left and right did not help. Actually, I entered the formula in a separate cell and copied over my first test cell later.

That was good. Now it worked. I could see the numbers or $0 as expected.

Let's now compute the columns' totals. I write another formula and it takes as a formula:

=SUM(J2:J173)

But instead of a total, I get the very first value (the one found in J2 in this case). I have to say, I don't recall ever seeing that before?! What could the problem be?

Well, the first expression above did copy the value from a text column and it is shown as a formula, but it has not changed the type. The value in my J column is still text instead of a value.

What I had to do is select the values in the source column (I in my case) and do a conversion. I used the menu Data » Text to Columns ... and it worked. It transformed those cells from being plain text to numbers. Now my sums were working. Great!

Also I think that's annoying, but maybe I imported the wrong way. I'd have to try again from scratch to see whether there is a flag in there to define the type of value. It could also be that the source included "$123.45" and Cacl sees those parenthesis to mean "text" as the type of the value even if it looks like a currency number.

One feature that will help you a lot in this case, in the View menu, look for Value Highlighting (you can also use Ctrl-F8). That changes the color of the data to one of three colors:

• Black — Text
• Blue — Values (numbers)
• Green — Formula

So you see each cell using those colors instead of the standard everything is black (or red for negative numbers). That allows you to quickly find the columns or rows with issues.