Number Format?

I have entered the following in cell A1 ="Average "&AVERAGE(D: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
Thu May 08 11:23:27 CDT 2008

Try this:

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

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

Regards,

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


"gb_S49" <gbS49@discussions.microsoft.com> wrote in message
news:2F9802AE-E0EE-4F82-9379-10F310AD44E4@microsoft.com...
> I have entered the following in cell A1 ="Average "&AVERAGE(D: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
> :-(
>

Gaurav
Thu May 08 11:29:57 CDT 2008

Try this

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


"gb_S49" <gbS49@discussions.microsoft.com> wrote in message
news:2F9802AE-E0EE-4F82-9379-10F310AD44E4@microsoft.com...
>I have entered the following in cell A1 ="Average "&AVERAGE(D: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
> :-(
>

MikeH
Thu May 08 11:28:23 CDT 2008

Maybe

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

Mike

"gb_S49" wrote:

> I have entered the following in cell A1 ="Average "&AVERAGE(D: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
> :-(
>

MikeH
Thu May 08 11:29:28 CDT 2008

You wanted 1 decimal place so

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

Mike

"gb_S49" wrote:

> I have entered the following in cell A1 ="Average "&AVERAGE(D: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
> :-(
>

gbS49
Thu May 08 11:39:01 CDT 2008

Perfect
Many Thanks
:-)

"Ron Coderre" wrote:

> Try this:
>
> A1 ="Average "&TEXT(AVERAGE(D:D),"0.0")
>
> Does that help?
> Post back if you have more questions.
> --------------------------
>
> Regards,
>
> Ron
> Microsoft MVP (Excel)
> (XL2003, Win XP)
>
>
> "gb_S49" <gbS49@discussions.microsoft.com> wrote in message
> news:2F9802AE-E0EE-4F82-9379-10F310AD44E4@microsoft.com...
> > I have entered the following in cell A1 ="Average "&AVERAGE(D: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
> > :-(
> >
>

gbS49
Thu May 08 11:39:02 CDT 2008

Perfect
Many Thanks
:-)


"gb_S49" wrote:

> I have entered the following in cell A1 ="Average "&AVERAGE(D: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
> :-(
>

gbS49
Thu May 08 11:40:01 CDT 2008

Perfect
Many Thanks
:-)


"gb_S49" wrote:

> I have entered the following in cell A1 ="Average "&AVERAGE(D: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
> :-(
>

Rick
Thu May 08 11:38:11 CDT 2008

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" <gbS49@discussions.microsoft.com> wrote in message
news:2F9802AE-E0EE-4F82-9379-10F310AD44E4@microsoft.com...
>I have entered the following in cell A1 ="Average "&AVERAGE(D: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
> :-(
>

gbS49
Thu May 08 11:40:07 CDT 2008


Many Thanks
:-)


"gb_S49" wrote:

> I have entered the following in cell A1 ="Average "&AVERAGE(D: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
> :-(
>

David
Thu May 08 12:44:31 CDT 2008

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

"Rick Rothstein (MVP - VB)" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in
message news:%236krfnSsIHA.4076@TK2MSFTNGP06.phx.gbl...
> 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" <gbS49@discussions.microsoft.com> wrote in message
> news:2F9802AE-E0EE-4F82-9379-10F310AD44E4@microsoft.com...
>>I have entered the following in cell A1 ="Average "&AVERAGE(D: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
>> :-(
>>
>

Rick
Thu May 08 12:53:08 CDT 2008

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


"David Biddulph" <groups [at] biddulph.org.uk> wrote in message
news:482337f6$1_1@glkas0286.greenlnk.net...
> Are you sure, Rick? What effect does that have when the formula gives a
> text result?
> --
> David Biddulph
>
> "Rick Rothstein (MVP - VB)" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in
> message news:%236krfnSsIHA.4076@TK2MSFTNGP06.phx.gbl...
>> 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" <gbS49@discussions.microsoft.com> wrote in message
>> news:2F9802AE-E0EE-4F82-9379-10F310AD44E4@microsoft.com...
>>>I have entered the following in cell A1 ="Average "&AVERAGE(D: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
Thu May 08 13:01:25 CDT 2008

On Thu, 8 May 2008 12:38:11 -0400, "Rick Rothstein \(MVP - VB\)"
<rick.newsNO.SPAM@NO.SPAMverizon.net> wrote:

>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:D)

And then used the Custom Format:

"Average "0.0

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

Rick
Thu May 08 13:08:31 CDT 2008

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


"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message
news:spf624tjid42l4hu5kbsdvkga3su1qo2f2@4ax.com...
> On Thu, 8 May 2008 12:38:11 -0400, "Rick Rothstein \(MVP - VB\)"
> <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote:
>
>>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:D)
>
> And then used the Custom Format:
>
> "Average "0.0
>
> the result would be as he might want.
> --ron