concatenation

[charijit]

Dear Members,

In a spreadsheet I have

A1 = Max(E1,F1,G1) = +24
B1 = Min(E2,F2,G2) = -25

I want C1 to show

C1 = +24/-25.

Kindly tell me how to do it.

 

[al_b_cnu]

Hi Arijit, You mean something like:

Book1
	A	B	C	D	E	F	G
1	24	-25	-0.96		21	23	24
2					-25	-24	-23
Sheet3

Formula in C1 is =A1/B1

 

[venkat1926]

=A1/A2

actulaly A1----=MAX(E1:G1)
A2 is =MIN(E2:G2) or


=MAX(E1:G1)/MIN(E2:G2)

 

[Aladin Akyurek]

=LOOKUP(SIGN(A1),{-1,"";0,"";1,"+"})&A1&"/"&LOOKUP(SIGN(B1),{-1,"";0,"";1,"+"})&B1

 

[al_b_cnu]

Another option, depending on what you want, is

=A1/B1 and format / cell / fraction

 

[barry houdini]

I read it the same as Aladin so I'd suggest

=IF(A1>0,"+","")&A1&"/"&IF(B1>0,"+","")&B1

 

[charijit]

Thanks to Alladin and Barry. Although I could understand Barry's solution well because I don't know anything about arrays, but nonetheless I am grateful to both of you.

 

[charijit]

Further to my above question, I wish to also discriminate between maxima's and minima's of the same sign. Like,

if, A1=max(......)=+25 and B1=min(.....)=-25 then C1=+25/-25 but what if A1=+26 and B1=+2 then C1=+26 only rather than C1=+26/+2.

 

[NBVC]

I assume you used Barry's solution:

Therefore change it to this:

=IF(A1>0,"+","")&A1&IF(B1>0,"","/"&B1)

And if you don't want 0 minimum to show, then:

=IF(A1>0,"+","")&A1&IF(B1>=0,"","/"&B1)

 

[charijit]

Thank you NBVC for the solution (partial), but I think I didn't make things clear. By the way, I also understood Aladin's solution.

Here is what I meant:
If A1=+5 and B=+9 then C1=+9 (Maxima)
If A1=-5 and B=-9 then C1=-9 (Minima)
If A1=-9 and B=+2 then C1=-9/+2 (both maxima and minima)
It is also a requirement that if anyone of A1 or B1 is 0 then it should only show the maxima or minima with its sign.