Convert number output based on text value

[psychward]

I have some transaction data with in and out entries, ie. “Buy” and “Sell”, but they are all listed in positive figures except for a separate column that states Buy or Sell.

I’ve been at this for 4 hours now and I can’t figure out a formula that takes the entry amount and turns it into a negative if it’s labeled as sell. I’ve tried converting the textstrings to numbers, giving them boolean values etc, my logic steps are depleted.

I’d like to keep the data intact, if possible.

Please for the sake of my sanity, help.

Example data:

2021-05-11	Potato  BUY    5.21	€13.51
2021-05-11	Potato  SELL   4.10	€12.11

[Edit - Opaque] Put Example data to preformatted text

Edit: Solved, my mistake was that I tried adding the function directly to the original cell, and so making it reference itself, giving the error 522. I guess this is a rookie error, but I thank you for getting me on the right track to solve it!

[anon73440385]

And you are about to use Calc? If yes - revise your tag common (which would mean you want a solution for all LibreOffice modules).

My gut tells me: €13.51 and €12.11 are not real numbers but text, hence something like =IFS(C1="BUY";E1;C1="SELL";-E1;1;E1) doesn’t work for you.

[LeroyG]

To see if €13.51 are or not a real number, click on that cell, and see if the formula bar show 13.51 (real number with currency format), or €13.51 (text).

Alternatively, press Ctrl+F8. See Help on Value Highlighting.

Edit your answer if do you want to add more information. Don’t use Add Answer to comment.

[keme]

Edit: Solved, my mistake was that I tried adding the function directly to the original cell, and so making it reference itself, giving the error 522. […]

That is a solution, not an adjustment/comment/supplemental info to the problem description, so I guess it should be posted as an answer below.

You solved it yourself. That is how we like it. Good on you!