Formatting a calculated value in a cell

[Tim]

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

 

[David Biddulph]

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?

 

[Bill Kuunders]

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")

 

[NBVC]

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

 

[Tim]

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.

 

[Tim]

Thank you.
It worked.

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")