Formatting for Two Decimal Places

[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 my
having to 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 Greenblatt]

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 my
having to 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?

Have you tried conditional formatting? (Format - conditional Formatting).
Look in Excel help for assistance, or post back her with more details about
what you tried and what you are trying to accomplish.

 

[Evan]

Have you tried conditional formatting? (Format - conditional Formatting).
Look in Excel help for assistance, or post back her with more details about
what you tried and what you are trying to accomplish.

Conditional formatting didn't work because I could not find where it
allows me to adjust number style (limited to font, border and
pattern). This motivated to try the formula approach, which worked to
a degree.

In cell F2 the employee logged 6 hours for the quarter, therefore, his
FTE amount showed up in cell O2 as 0.0 using the formatting I had set
up for the entire O column. Then I created the following formula:

=IF(F2/450<0.01,ROUNDUP(F2/450,3),ROUNDDOWN(F2/450,1))

The result in cell O2 was .014, which is exactly what I want.

Then I encountered a problem with the next row. This employee logged
the maximum 450 hours for the quarter so his FTE amount showed in O3
as 1.0, which is what I want. But when I plug in the same formula as I
used in O2

=IF(F3/450<0.01,ROUNDUP(F3/450,3),ROUNDDOWN(F3/450,1))

I get 1.000. What am I doing wrong in this formula that I get it
right in O2 but O3 adds two decimal places I don't want?

 

[JE McGimpsey]

In cell F2 the employee logged 6 hours for the quarter, therefore, his
FTE amount showed up in cell O2 as 0.0 using the formatting I had set
up for the entire O column. Then I created the following formula:

=IF(F2/450<0.01,ROUNDUP(F2/450,3),ROUNDDOWN(F2/450,1))

The result in cell O2 was .014, which is exactly what I want.

Then I encountered a problem with the next row. This employee logged
the maximum 450 hours for the quarter so his FTE amount showed in O3
as 1.0, which is what I want. But when I plug in the same formula as I
used in O2

=IF(F3/450<0.01,ROUNDUP(F3/450,3),ROUNDDOWN(F3/450,1))

I get 1.000. What am I doing wrong in this formula that I get it
right in O2 but O3 adds two decimal places I don't want?

You're not doing anything "wrong", but you're mixing up values (the
result of the calculation) and formatting, which is (mostly) independent
of value.

What format are you using in the cells? My suggestion would be to use a
Custom Format like this:

Choose Format/Cells/Number/Custom and enter this in the Type box:

[<=0]0.0;[<=0.01]0.000;0.0

or

[<=0]0.0_0_0;[<=0.01]0.000;0.0_0_0

if you want the decimal points to align.