Using MIN and MAX to display results in same cell in specific format

[MS3217]

I want to display (in the same cell) the minimum and maximum values from a range of cells. The formula I am using to do that is:

'=MIN(range)&" - "&MAX(range)

The resulting output shown in the cell is:
1000 - 2000

I want the cell to display:
1,000 sf - 2,000 sf

What is best way to set up the formula to perform the minimum and maximum value search and format the cell to display the values as desired with the comma separator and sf after each value?

[ConneXionLost]

How about:

Please Login or Register  to view this content.

Cheers,

[AlKey]

Or maybe this

=TEXT(MIN(A1:A4),"#,0")&" sf - "&TEXT(MAX(A1:A4),"#,0")&" sf"

	A	B
1	2000	1,000 sf - 2,000 sf
2	1254
3	1000
4	1300

[mrice]

You can do this with a user defined function

Please Login or Register  to view this content.

Open the VBA editor by hitting Alt F11.
Insert a new module with Insert - Module
Paste in the above function
Go back to the sheet by hitting Alt F11.

In a suitable cell, enter = MinMax(A1:A10) for example.

Remember to save the workbook as a macro enabled workbook .xlsm


Or by a more conventional formula

=TEXT(MIN(A1:A10),"0,000") & " sf - " & TEXT(MAX(A1:A10),"0,000") & " sf"

[MS3217]

Thanks for the reply and info!