Hi all, I want to write a custom format for a cell in excel. I want to be able to enter a sum say, =300/8 and to format the cell so that Lm/W follows this automatically. I have been using this format: #.#"Lm/W" which works well except when I enter 0 or if the calculation comes out at a round number I get 1.Lm/W Could someone please tell me how to correctly format the cell to round to one decimal place and to display 0 when 0 is either the outcome or entered number. I hope that makes sense Thanks Long Edit: Sorry I meant I get .Lm/W when I enter 0 and if the calculation comes to a whole number i.e. 10/10 then I get 1.Lm/W
Hmm, just tried using a custom formula using the following in Excel 2010 and it seems ok when inputting examples like 300/8 or 10/3. Note the space at the start " Lm/W" which makes it look a bit nicer. Cell formatting 'Custom': #.#" Lm/W" Putting 300/8 into a cell results in 37.5 Lm/W When I type "0" into a cell it results in: .Lm/W which is a bit odd, no value displayed - don't know how to rectify that one off the top of my head. Not sure why you are getting 1.Lm/W, maybe something to do with the decimal adjustment? Which version are you using?
Ahh sorry it's my fault, I didn't read it properly! Curious now... going to do some more investigation!
Hmm, having read a bit more about number formats in Excel, you can specify formatting for positive, negative, zero and text values (in that order), separated with a semicolon. Try this as the custom formatting: 0.0" Lm/W"; -0.0" Lm/W"; 0" Lm/W"; "INVALID ENTRY" The last bit isn't really required, but could serve as a useful visual indicator in case you accidentally enter a text value instead of a number.
You f*******ing legend! Thanks, I did try to google it but couldn't think of what to ask! +1 rep for you
Sure, I pieced it together after reading a few different pages: http://superuser.com/questions/205759/in-excel-format-number-with-optional-decimal-places http://www.excelforum.com/excel-general/555029-how-to-only-show-decimals-if-there-are-any.html http://peltiertech.com/Excel/NumberFormats.html
