Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting a calculated value in a cell
I have the following array formula (Excel 2003) in a cell:
=AVERAGE(IF(WEEKDAY(Seven)=WEEKDAY($B283),$B$3:$B$ 262))&" per "&TEXT($B283,"dddd") where "Seven" is a named range for all the days in the year 2007 & column B has the production #s for each day & B283 to B289 has the days of the week. This gives the answer I am looking for: 76.2307692307692 per Monday However, how can I format the output to 2, or 3, decimal places? Format, Cells, Number does not work and I cannot come up with a custom format either. Thank you |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting a calculated value in a cell
You say that Format, Cells, Number "does not work".
What do you mean by "does not work"? If you apply it to a cell containing the formula giving the answer 76.2307692307692 and you request a format with 2 or 3 decimal places, what error message do you get, or if no error message, what unexpected result? -- David Biddulph "Tim" wrote in message ... I have the following array formula (Excel 2003) in a cell: =AVERAGE(IF(WEEKDAY(Seven)=WEEKDAY($B283),$B$3:$B$ 262))&" per "&TEXT($B283,"dddd") where "Seven" is a named range for all the days in the year 2007 & column B has the production #s for each day & B283 to B289 has the days of the week. This gives the answer I am looking for: 76.2307692307692 per Monday However, how can I format the output to 2, or 3, decimal places? Format, Cells, Number does not work and I cannot come up with a custom format either. Thank you |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting a calculated value in a cell
add round(...................................,2)
to your formula you'll get =ROUND(AVERAGE(IF(WEEKDAY(Seven)=WEEKDAY($B283),$B $3:$B$262)),2) &" per "&TEXT($B283,"dddd") -- Greetings from New Zealand "Tim" wrote in message ... I have the following array formula (Excel 2003) in a cell: =AVERAGE(IF(WEEKDAY(Seven)=WEEKDAY($B283),$B$3:$B$ 262))&" per "&TEXT($B283,"dddd") where "Seven" is a named range for all the days in the year 2007 & column B has the production #s for each day & B283 to B289 has the days of the week. This gives the answer I am looking for: 76.2307692307692 per Monday However, how can I format the output to 2, or 3, decimal places? Format, Cells, Number does not work and I cannot come up with a custom format either. Thank you |
#4
|
|||
|
|||
Quote:
=TEXT(AVERAGE(IF(WEEKDAY(Seven)=WEEKDAY($B283),$B$ 3:$B$262)),"0.00")&" per"&TEXT($B283,"dddd") confirmed with CSE key combo |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting a calculated value in a cell
Should have clarified: "does not work" means that Excel does not give any
errors. It accepts the formatting change to "Number", 2 decimal places, but the display does not change. "David Biddulph" wrote: You say that Format, Cells, Number "does not work". What do you mean by "does not work"? If you apply it to a cell containing the formula giving the answer 76.2307692307692 and you request a format with 2 or 3 decimal places, what error message do you get, or if no error message, what unexpected result? -- David Biddulph "Tim" wrote in message ... I have the following array formula (Excel 2003) in a cell: =AVERAGE(IF(WEEKDAY(Seven)=WEEKDAY($B283),$B$3:$B$ 262))&" per "&TEXT($B283,"dddd") where "Seven" is a named range for all the days in the year 2007 & column B has the production #s for each day & B283 to B289 has the days of the week. This gives the answer I am looking for: 76.2307692307692 per Monday However, how can I format the output to 2, or 3, decimal places? Format, Cells, Number does not work and I cannot come up with a custom format either. Thank you |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting a calculated value in a cell
Thank you.
It worked. "Bill Kuunders" wrote: add round(...................................,2) to your formula you'll get =ROUND(AVERAGE(IF(WEEKDAY(Seven)=WEEKDAY($B283),$B $3:$B$262)),2) &" per "&TEXT($B283,"dddd") -- Greetings from New Zealand "Tim" wrote in message ... I have the following array formula (Excel 2003) in a cell: =AVERAGE(IF(WEEKDAY(Seven)=WEEKDAY($B283),$B$3:$B$ 262))&" per "&TEXT($B283,"dddd") where "Seven" is a named range for all the days in the year 2007 & column B has the production #s for each day & B283 to B289 has the days of the week. This gives the answer I am looking for: 76.2307692307692 per Monday However, how can I format the output to 2, or 3, decimal places? Format, Cells, Number does not work and I cannot come up with a custom format either. Thank you |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting on calculated text | Excel Worksheet Functions | |||
Formatting numbers in each cell after I have calculated their valu | Excel Worksheet Functions | |||
Calculated Field and Calculated Item in Pivot Table | Excel Discussion (Misc queries) | |||
pivot table formulas for calculated field or calculated item | Excel Discussion (Misc queries) | |||
how to change a calculated cell to = the calculated value | Excel Discussion (Misc queries) |