SOLVED

If formula

Occasional Visitor

Hello,

I need a function to check a fact, for example A1>0 : if true I need it to write "Text" plus the number of A1; and if false write "text" and A1*(-1). Is this possible?

Thanks

4 Replies
best response confirmed by Diogo______ (Occasional Visitor)
Solution

@Diogo______ Perhaps like this?

Screenshot 2021-11-24 at 06.22.39.png

 

= IF(
     value>0, 
     "Value negated is " & TEXT(value*(-1),"0"), 
     "Non-positive value"
  )

@Diogo______ 

The text may not appeal to you but I am sure it can be sorted.

@Diogo______  wrote:  ``if false write "text" and A1*(-1)``

 

Odd that you marked Riny's answer as "best response".  If A1>0 is FALSE (i.e. A1 is negative), you specifically asked for -A1, which makes it appear positive.  Yet Riny's example with -20 in A1 shows that his formula returns the negative value (-20).

 

Also, Riny's use of the TEXT function rounds A1 to an integer. But you gave no indication that that is what you want.

 

I would suggest:

 

="Text " & ABS(A1)

 

Aside....  There is never any reason to write A1*(-1).  Simply write -A1.

@Joe User 

You are correct. Neither @Riny_van_Eekelen  nor I read the original post with sufficient care.  I was wondering whether the 'text' mentioned in each case was the same text or different text.  I didn't notice that the value A1 was wanted either way.  ABS(value) would give the required result.  I often use number formatting to display text without actually changing the underlying number.

 

On the issue of the value*(-1), this is often used as a convention to ensure the reader is aware of the sign change.  It is done in the full knowledge that it reduces to -value.