Excel 2007 : rounding off numbers when a symbol is added to the cell

[jimb01]

In cell N19 the number there is rounded down to .004. Cell i19 is < symbol + N19 only it is showing it as <0.004495333. I want it to show it as <.004. How do I make it round the number off to the nearest thousanth with having a < symbol added in the cell?

[zbor]

Here, try this:

=IF(L19>5, P19, "<"&ROUND(N19, 3))

Hint: no need for two IF functions. Check how it work.

[Ron Coderre]

Here are two approaches.

1) a formula that returns formatted text
I19: =IF(L19>5,P19,"<"&ROUND(N19,3))

I OFFERED A BETTER 2ND OPTION IN THE SUBSEQUENT FOLLOW-UP POST
2) a formula that returns the referenced number and simply displays a leading symbol.
I19: =IF(L19>5,P19,N19)
Format that cell with a custom number format:
• Format.Format_cells.Number_tab
Category: Custom
Type: <0.000
Click: OK

That cell's value will still be numeric and can be used in calculations.

Is that something you can work with?

[Ron Coderre]

Actually, the custom number format wouldn't work the way you want it to.

A better second option would be:
2) Use conditional formatting to display the I19 number
• Home.Conditional_formatting...Use a formula todetermine which cells to format
• Formula: =($L19<=5)
• Click: Format...Number_tab
...Category: Custom
...Type: <0.000
Click: OK...click: OK

That conditional format will only engage when the Col_L value is <=5

I hope that helps.