Rounding Problem

[Evan]

I create a quarterly report for my client showing how much time almost
100 individual employees worked on that client's projects in that
quarter. In the report I provide the gross number of hours plus
convert those hours to a FTE (full-time equivalent) basis by dividing
the gross hours by 450 (150 hours per month x 3).

For simplicity's sake, I round to one decimal point the FTE amount for
each employee, however, when I do this there are some employees whose
hours in that quarter are so low that they appear as a 0 FTE.

For these cases I want to take the FTE amount out to another decimal
point . To do so I want to have a formula that does it rather than go
through by hand to reformat each of these outliers. I suspect it
ought to be an IF formula but I don't know how to include a formatting
function within it that expands the number of decimal places to 2.

Can this even be done?

 

[Bob Umlas]

You can format based on the value. For example, if the value is <100 and you
want 2 decimal places (and 1 otherwise):
Format/Number/Custom:
[<100]0.00;0.0

Bob Umlas

 

[JoeU2004]

For simplicity's sake, I round to one decimal point the FTE amount for
each employee, however, when I do this there are some employees whose
hours in that quarter are so low that they appear as a 0 FTE.

For these cases I want to take the FTE amount out to another decimal
point .

The following might work for you:

=if(and(round(A1/450,2)>0,round(A1/450,1)=0)),
text(A1/450,"0.00"), text(A1/450,"0.0"))

formatted with Right horizontal alignment.

Note: That results in "0.0" if an employee bills only 1 or 2 minutes. You
need 3 decimal places in those cases.

If A1 no employee will bill only 1 or 2 minutes, the following might
suffice:

=if(and(A1>0,round(A1/450,1)=0),
text(A1/450,"0.00"), text(A1/450,"0.0"))

If A1 might be "", you will need the following:

=if(and(n(A1)>0,round(n(A1)/450,1)=0),
text(A1/450,"0.00"), text(n(A1)/450,"0.0"))

and similarly for the first formula. You do not need text(n(A1)/450,"0.00")
because in that case, A1 cannot be "".

BTW, these formulas result in text. Excel is not consistent in its
treatment of "numeric text" in arithmetic expressions and comparisons and as
parametes to functions. If the formula above is in B1, it would be prudent
to use --B1 in subsequent expressions.


----- original message -----

 

[JoeU2004]

You can format based on the value. For example,
if the value is <100 and you want 2 decimal places
(and 1 otherwise):
Format/Number/Custom:
[<100]0.00;0.0

Relevant to Evan's problem, I presume you have the following in mind:

[<0.005]0.0;[<0.05]0.00;0.0

That formats 0 as 0.0 as well as 1/450 and 2/450, which I presume Evan would
want.

If Evan wants 1/450 and 2/450 to be formatted with 3 dp, but 0 to be
formatted with 1 dp, can that be done with a custom format?

The following results in an error in Excel 2003:

[=0]0.0;[<0.005]0.000;[<0.05]0.00;0.0

I presume that custom formats have a limit of 3 alternatives.


----- original message -----

You can format based on the value. For example, if the value is <100 and
you want 2 decimal places (and 1 otherwise):
Format/Number/Custom:
[<100]0.00;0.0

Bob Umlas

I create a quarterly report for my client showing how much time almost
100 individual employees worked on that client's projects in that
quarter. In the report I provide the gross number of hours plus
convert those hours to a FTE (full-time equivalent) basis by dividing
the gross hours by 450 (150 hours per month x 3).

For simplicity's sake, I round to one decimal point the FTE amount for
each employee, however, when I do this there are some employees whose
hours in that quarter are so low that they appear as a 0 FTE.

For these cases I want to take the FTE amount out to another decimal
point . To do so I want to have a formula that does it rather than go
through by hand to reformat each of these outliers. I suspect it
ought to be an IF formula but I don't know how to include a formatting
function within it that expands the number of decimal places to 2.

Can this even be done?

 

[JoeU2004]

Errata....

Ignore my suggestion.

Bob's approach (custom formatting) is far superior. I did not understand
how to apply it to your problem when I first read Bob's posting.


----- original message -----