concatenation

charijit

Board Regular
Dear Members,

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.

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Hi Arijit, You mean something like:
Book1
ABCDEFG
124-25-0.96212324
2-25-24-23
Sheet3

Formula in C1 is =A1/B1

=A1/A2

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

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

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

Another option, depending on what you want, is

=A1/B1 and format / cell / fraction

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

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.

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.

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)

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.

Replies
5
Views
153
Replies
3
Views
97
Replies
8
Views
389
Replies
1
Views
187
Replies
2
Views
226

1,207,392
Messages
6,078,223
Members
446,323
Latest member
fishezuk

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back