SOLVED

If formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2999599%22%20slang%3D%22en-US%22%3EIf%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2999599%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EHello%2C%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20need%20a%20function%20to%20check%20a%20fact%2C%20for%20example%20A1%26gt%3B0%20%3A%20if%20true%20I%20need%20it%20to%20write%20%22Text%22%20plus%20the%20number%20of%20A1%3B%20and%20if%20false%20write%20%22text%22%20and%20A1*(-1).%20Is%20this%20possible%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EThanks%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2999599%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2999892%22%20slang%3D%22en-US%22%3ERe%3A%20If%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2999892%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1228066%22%20target%3D%22_blank%22%3E%40Diogo______%3C%2FA%3E%26nbsp%3BPerhaps%20like%20this%3F%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-11-24%20at%2006.22.39.png%22%20style%3D%22width%3A%20345px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F329077i0C7604734FA98EA5%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-11-24%20at%2006.22.39.png%22%20alt%3D%22Screenshot%202021-11-24%20at%2006.22.39.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3000845%22%20slang%3D%22en-US%22%3ERe%3A%20If%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3000845%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3D%20IF(%0A%20%20%20%20%20value%26gt%3B0%2C%20%0A%20%20%20%20%20%22Value%20negated%20is%20%22%20%26amp%3B%20TEXT(value*(-1)%2C%220%22)%2C%20%0A%20%20%20%20%20%22Non-positive%20value%22%0A%20%20)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1228066%22%20target%3D%22_blank%22%3E%40Diogo______%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20text%20may%20not%20appeal%20to%20you%20but%20I%20am%20sure%20it%20can%20be%20sorted.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3001875%22%20slang%3D%22en-US%22%3ERe%3A%20If%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3001875%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1228066%22%20target%3D%22_blank%22%3E%40Diogo______%3C%2FA%3E%26nbsp%3B%20wrote%3A%26nbsp%3B%20%60%60if%20false%20write%20%22text%22%20and%20A1*(-1)%60%60%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOdd%20that%20you%20marked%20Riny's%20answer%20as%20%22best%20response%22.%26nbsp%3B%20If%20A1%26gt%3B0%20is%20FALSE%20(i.e.%20A1%20is%20negative)%2C%20you%20specifically%20asked%20for%20-A1%2C%20which%20is%20makes%20it%20appear%20positive.%26nbsp%3B%20Yet%20Riny's%20example%20with%20-20%20in%20A1%20shows%20that%20his%20formula%20returns%20the%20negative%20value%20(-20).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%2C%20Riny's%20use%20of%20the%20TEXT%20function%20rounds%20A1%20to%20an%20integer.%20But%20you%20gave%20no%20indication%20that%20that%20is%20what%20you%20want.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20suggest%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23FF0000%22%3E%3D%22Text%20%22%20%26amp%3B%20ABS(A1)%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAside....%26nbsp%3B%20There%20is%20never%20any%20reason%20to%20write%20A1*(-1).%26nbsp%3B%20Simply%20write%20-A1.%3C%2FP%3E%3C%2FLINGO-BODY%3E
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.