Adding text to cell with formula changes rounding

[natrlhy]

Hello all,

Here's my scenario:

In the cell I originally have this function: =SUM(H7:H85)/1024/1024

The output in the cell is: 1,284.78 (formatting is done by right clicking on the cell and changing the output there).

When I add text to the cell I have this function: =SUM(H7:H85)/1024/1024&" GB"

Output is now this: 1284.77679729461 GB

How do I get it to retain the formatting and round it properly?

Thanks!

 

[VoG]

Hello and welcome to MrExcel.

Try

=TEXT(SUM(H7:H85)/1024/1024,"0.00")&" GB"

 

[BAlGaInTl]

Try

=ROUND(SUM(H7:H85)/1024/1024,2) & " GB"

 

[natrlhy]

Thank you to you both! I tried the ROUND function but I must not of had the syntax right even though excel didn't complain.

Both of these work!

Thanks again!

 

[natrlhy]

How about when using the AVERAGE function? I'm trying to average some time entries:

Before: =AVERAGE(I7:I84)
Output: 01:02

After: ="Avg. Time: "&AVERAGE(I7:I84)
Output: Avg. Time: 0.043207994579945799

I tried this: ="Avg. Time: "&TEXT(AVERAGE(I7:I84),"00:00") and only get #VALUE! in the cell.

Thanks!

 

[VoG]

Try

="Avg. Time: "&TEXT(AVERAGE(I7:I84),"hh:mm")

 

[natrlhy]

Again, worked perfectly! Thank you!

 

[natrlhy]

With regards to the =ROUND(SUM(H7:H85)/1024/1024,2) & " GB" or =TEXT(SUM(H7:H85)/1024/1024,"0.00")&" GB" how do I get it to retain the commas when the enough digits warrant it?

 

[VoG]

Try

=TEXT(SUM(H7:H85)/1024/1024,"#,##0.00")&" GB"

 

[barry houdini]

Try FIXED function, i.e.

=FIXED(SUM(H7:H85)/1024/1024)&" GB"