FORMATING WITHIN CELL

[BIG D]

I HAVE SALES DATA IN COLUMNS C,D AND E AND MY ON HAND COUNT IN COLUMN
F. THE DATA IN MOSTLY 2 DIGIT WITH SOME 5 DIGIT BUT NOTHING ABOVE 5
DIGIT. THE FORMULA BELOW IS IN COLUMN H. I AM GETTING THE CORRECT
RESULTS FROM THE FORMULA BUT BECAUSE OF THE RESULTANT DIGIT COUNT
ASSOCIATED WITH EACH PART OF THE FORMULA THE TEXT DOES NOT ALIGN AS I
WOULD LIKE. I NEED TO TAB OR "PAD" THE RESULTS SO THEY ALIGN IN A MORE
READABLE FORMAT.

=("MAX"&" "&(MAX(C3:E3)))&" "&("MIN"&" "&(MIN(C3:E3)))&" "&("AVG"&"
"&(AVERAGE(C3:E3)))&" "&"ON HAND"&" "&F3. I KNOW IT IS UGLY BUT IT
WORKS!

REGARDS

BIG D

 

[Pete_UK]

Next time, please press the Caps lock key - it is considered rude to
SHOUT.

First of all, your formula can be reduced:

="MAX "&MAX(C3:E3)&" MIN "&MIN(C3:E3)&" AVG " &AVERAGE(C3:E3)&" ON
HAND "&F3

However, you need to make changes like this:

="MAX "&TEXT(MAX(C3:E3),"00000")&" MIN "&TEXT(MIN(C3:E3),"00000")&"
AVG " &TEXT(AVERAGE(C3:E3),"00000")&" ON HAND "&TEXT(F3,"00000")

The TEXT function will put each number in a 5-digit field.

Hope this helps.

Pete

 

[Harlan Grove]

. . . BUT NOTHING ABOVE 5 DIGIT. . . . ....
. . . I NEED TO TAB OR "PAD" THE RESULTS SO THEY ALIGN IN A MORE
READABLE FORMAT.

....

Don't shout.

Presumably you'd like to avoid leading zeros. If so, try

="MAX "&TEXT(MAX(C3:E3),"?????")&" MIN "&TEXT(MIN(C3:E3),"?????")
&" AVG "&TEXT(AVERAGE(C3:E3),"?????")&" ON HAND "&TEXT(F3,"?????")

 

[Pete_UK]

Harlan,

won't the spaces be a different width and therefore repeated lines
will be misaligned with different digit numbers?

Pete

 

[Harlan Grove]

Harlan,

won't the spaces be a different width and therefore repeated lines
will be misaligned with different digit numbers?

....

Not in a monospace typeface. If the OP is trying to do this with a
proportional typeface, the OP is crazy to do this in a single string
formula rather than spreading it across multiple columns.

 

[BIG D]

...

Not in a monospace typeface. If the OP is trying to do this with a
proportional typeface, the OP is crazy to do this in a single string
formula rather than spreading it across multiple columns.

Thanks Guys. Both mods worked great and I now have what I need. Sorry
for the shout, just a bad habit of mine. Will try to do better in the
future.

Regards

Big D