Number Format?

[gb_S49]

I have entered the following in cell A1 ="Average "&AVERAGE(D)
and the value I get is Average 47.247619047619.
How can I round down to 1 decimal place?
I have tried number format but it does not make a difference
:-(

 

[Ron Coderre]

Try this:

A1 ="Average "&TEXT(AVERAGE(D),"0.0")

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Mike H]

Maybe

="Average "&TEXT(AVERAGE(D),"0.00")

Mike

 

[Mike H]

You wanted 1 decimal place so

="Average "&TEXT(AVERAGE(D),"0.0")

Mike

 

[Gaurav]

Try this

="Average "&ROUND(AVERAGE(D),2)

 

[Rick Rothstein \(MVP - VB\)]

If you want to physically round the value to one decimal place (that is,
reduce its accuracy), then you have your answer in the other postings in
this thread. However, if you only want to display it to one decimal place,
but keep its full accuracy (perhaps for other calculations), then leave keep
your formula but Custom Format the cell (right click the cell and select
Format Cells) using 0.0 as the pattern.

Rick

 

[gb_S49]

Perfect
Many Thanks

Try this:

A1 ="Average "&TEXT(AVERAGE(D),"0.0")

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[gb_S49]

Perfect
Many Thanks

 

[gb_S49]

Perfect
Many Thanks

 

[gb_S49]

Many Thanks

 

[David Biddulph]

Are you sure, Rick? What effect does that have when the formula gives a
text result?

 

[Rick Rothstein \(MVP - VB\)]

Sooo, that is why everyone gave the TEXT function solution.<g>

I read too fast... totally missed the "Average "& part of his formula.
Thanks for catching that.

Rick

 

[Ron Rosenfeld]

If you want to physically round the value to one decimal place (that is,
reduce its accuracy), then you have your answer in the other postings in
this thread. However, if you only want to display it to one decimal place,
but keep its full accuracy (perhaps for other calculations), then leave keep
your formula but Custom Format the cell (right click the cell and select
Format Cells) using 0.0 as the pattern.

Rick

I don't believe that will work since his formula is a concatenation of strings.

However, if he changed his formula to :

=AVERAGE(D)

And then used the Custom Format:

"Average "0.0

the result would be as he might want.
--ron

 

[Rick Rothstein \(MVP - VB\)]

Yes, David caught that too. I read the OP's post too fast (carelessly) and
missed the "Average "& part of his formula. Your suggestion is probably
what I would have offered had I read the posting more carefully. Thanks for
posting it.

Rick