concatenation

charijit

Board Regular
Joined
Nov 27, 2003
Messages
89
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.
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
=A1/A2

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


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

al_b_cnu

Well-known Member
Joined
Jul 18, 2003
Messages
4,494

ADVERTISEMENT

Another option, depending on what you want, is

=A1/B1 and format / cell / fraction
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
I read it the same as Aladin so I'd suggest

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

charijit

Board Regular
Joined
Nov 27, 2003
Messages
89

ADVERTISEMENT

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

Board Regular
Joined
Nov 27, 2003
Messages
89
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

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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

Board Regular
Joined
Nov 27, 2003
Messages
89
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,116
Messages
5,570,273
Members
412,316
Latest member
JabirS
Top