Concantonate - syntax

[Mel Smith]

I'm using the following formula: =IFS(J27>J26,"PLUS",J27<J26,"MINUS",J27=J26,"LEVEL") and what I would like to do is add the contents of cell K28 to display with "PLUS" or "MINUS"
for example to show MINUS the value of K28 but without the minus symbol (or plus symbol in the case of PLUS. If LEVEL there is nothing additional to display other.

i.e. MINUS 1, PLUS 2 etc.

Can anyone point me in the right direction, please?

Mel

 

[Peter_SSs]

Is this what you mean? If not please give more, and clearer, examples of exactly what you have and what you want.

=CHOOSE(SIGN(J27-J26)+2,"MINUS " & K28,"LEVEL","PLUS "&K28)

 

[AlanY]

try this

=IFS(J27>J26,"PLUS "&K28,J27<J26,"MINUS "&K28,J27=J26,"LEVEL")

 

[Mel Smith]

Thank you. Alan. I'm almost there, now having seen your reply.

Thank you Peter, but I couldn't follow your suggestion, which is probably due to my poor explanation.

What I wish to display with my formula is the word "MINUS", "PLUS" or LEVEL" along with the value of cell K28. If this value is a minus, I do not wish to display the actual 'minus' symbol. In other words, the following would be displayed: MINUS 1, or PLUS 2 or LEVEL

 

[Mel Smith]

I've managed to "create"(?) a work-around. In cell K29 I've put =ABS(K28) and amended the formula to read: =IFS(J27>J26,"PLUS"&" "& K28,J27<J26,"MINUS"&" "&K29,J27=J26,"LEVEL")
This now displays as e.g. PLUS 2 or MINUS 2 or LEVEL, which is what i was trying to achieve.

Thanks for your input.

Mel

 

[Peter_SSs]

How is the value in K28 obtained? Is it a formula? If so, what formula?

 

[Mel Smith]

Hello Peter,

A simple: =J27-J26

Mel

 

[Peter_SSs]

Then you can use that directly instead of referring to J26 and J27
Either

=CHOOSE(SIGN(K28)+2,"MINUS " & -K28,"LEVEL","PLUS "&K28)

or

=IFS(K28>0,"PLUS "&K28,K28<0,"MINUS "&-K28,1,"LEVEL")

or

=IF(K28>0,"PLUS "&K28,IF(K28<0,"MINUS " &-K28,"LEVEL"))

 

[Mel Smith]

Many thanks, Peter,

I've chosen your first solution, which is much simpler than my effort!

Mel

 

[Peter_SSs]

Yet another option might be to have the very simple formula

=K28

and format that cell with a Custom format like this.

21 11 25.xlsm
	K	L
28	-6	MINUS 6
Plus Minus

Cell Formulas
Range	Formula
K28	K28	=J27-J26
L28	L28	=K28