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

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

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

Try:

=TEXT(AVERAGE(IF(WEEKDAY(Seven)=WEEKDAY($B283),$B$ 3:$B$262)),"0.00")&" per"&TEXT($B283,"dddd")

confirmed with CSE key combo

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

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