| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 :-( |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Number Format?
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" wrote in message ... 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 :-( |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Number Format?
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 :-( |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Number Format?
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 :-( |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Number Format?
Try this
="Average "&ROUND(AVERAGE(D:D),2) "gb_S49" wrote in message ... 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 :-( |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Number Format?
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" wrote in message ... 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 :-( |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Number Format?
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" wrote in message ... 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 :-( |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Number Format?
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 :-( |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Number Format?
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 :-( |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Number Format?
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 :-( |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Number Format?
Are you sure, Rick? What effect does that have when the formula gives a
text result? -- David Biddulph "Rick Rothstein (MVP - VB)" wrote in message ... 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" wrote in message ... 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 :-( |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Number Format?
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 ... Are you sure, Rick? What effect does that have when the formula gives a text result? -- David Biddulph "Rick Rothstein (MVP - VB)" wrote in message ... 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" wrote in message ... 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 :-( |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Number Format?
On Thu, 8 May 2008 12:38:11 -0400, "Rick Rothstein \(MVP - VB\)"
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 |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Number Format?
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" wrote in message ... On Thu, 8 May 2008 12:38:11 -0400, "Rick Rothstein \(MVP - VB\)" 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Convert numbers from text format to number format | Excel Discussion (Misc queries) | |||
| Replace million-billion number format to lakhs-crores format | Excel Discussion (Misc queries) | |||
| convert text-format number to number in excel 2000%3f | Excel Discussion (Misc queries) | |||
| How do i change numbers in text format to number format? | New Users to Excel | |||
| excel format cells/Number/Category: Number problem | Excel Discussion (Misc queries) | |||
Linear Mode
