Formatting a number in a formula

[jimb01]

How do I get I20 to = <0.040? Right now I can only get it to show 0.04. I have the cell formatted for number (3 decimal places). This is the formula I am usingfor I20 is: =IF(H20="","",IF(H20=""&"/"&K20,"",IF(E20="Blank","Blank",IF(L20>5,N20,IF(L20<=5,"<"&ROUND(N20,3)))))). The fomula for N20is: =IF(L20="","",IF(L20<=5,(0.055*49.04)/G20,IF(L20>=5.5,(L20/K20)*49.04/G20))). The answer for N20 cameout to be 0.040 Why can't I get I20 to = <0.040? Thank you for your assitance.

Jim

[JBeaucaire]

The problem is you're inserting a text character into the cell, "<" and that means the result is no longer numeric, it's all text.

="<" & ROUND(N20,3) ... is a text string that just "looks" numeric. It's not.

Fortunately Excel gives you the TEXT() function to format text-numbers.


= "<" & TEXT(ROUND(N20,3),"0.000")



=IF(H20="","",IF(H20=""&"/"&K20,"",IF(E20="Blank","Blank",IF(L20>5,N20,IF(L20<=5,"<"&TEXT(ROUND(N20,3),"0.000"))))))

[JBeaucaire]

When you joined the forum recently you agreed to abide by the Forum Rules, but in haste I fear you might not have actually read them. Please stop and take a moment to read them now. We all follow these rules for the benefit of all, as must you. Thanks.

(link above in the menu bar)

-----------------
Your post did not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.


I changed your title for you this time.

[JBeaucaire]

Thanks for the feeback.

I've marked this thread as SOLVED for you.
Next time, select Thread Tools from the links above and mark this thread as SOLVED. Thanks.