Number Formatting within an If statement

  • Thread starter Thread starter David
  • Start date Start date
D

David

I'm creating a report that flips the numerator and
denominator of a formula when a 1 is typed in a designated
cell. Since the results of the formula might be very small
or very large, I would like to control the display of
decimal places given for each result.

Ex.
Worked Hours = 550
Customers = 95000

(Worked Hours / Customers)*100 = 0.58
Customers / Worked Hours = 172.72

The would like the results be 0.53 and 172 respectively.
And I noticed conditional formatting does not include
number formats.

Can anyone help??
Thanks
 
David,

With
550 in A22
95000 in B22
1 or x in C22

=IF(C22=1,A22/B22*100,B22/A22)

Number>Format the target cell to two decimals.

HTH
Anders Silvén
 
I assume this is not just a display question, it's about desired precision. Like "is ten
dollars important" ? If you are broke and hungry, yes, if you are Bill Gates, no. The
concept of "Significant digits" considers the scale and uses only the numbers that
matters.

You can round a number to X significant digits and set the cell number format to General
(=no decimals spesified). With the result in A1, formula for 3 digits in B1:

=ROUND(A1,2-INT(LOG(A1)))

0.333333333333 will display 0.333
0.53 will display 0.53
172.72 will display 173 (rounds up)
1234 will display 1230 (rounds down)

Change the formula's 2 to 3 for one more significant digit.
 
David,

If you don't mind the result being text as against numeric, using the
previous designated cells you could use

=IF(C22=1,TEXT(A22/B22*100,"#0.00"),TEXT(ROUNDDOWN(B22/A22,0),"0"))

A few points
- I've assumed your result of .53 was a typo and should have been .58
- you round down the 172.72 result to 172. I assume this was deliberate so I
included ROUNDDOWN in the solution
- the second TEXT is not absolutely necessary as ROUNDDOWN strips the
decimals, you could use
=IF(C22=1,TEXT(A22/B22*100,"#0.00"),ROUNDDOWN(B22/A22,0)), but I kept it for
consistency

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
=(Worked Hours / Customers)*100 , same as you had......
=rounddown((Customers / Worked Hours),0)

No Conditional Formatting required.......

Vaya con Dios,
Chuck, CABGx3
 
Back
Top