| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 47311 | 2004-07-22 13:14:00 | Excel Formula | bk T (215) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 254360 | 2004-07-22 13:14:00 | I created a simple formula for cell G5 e.g. =E5-C5 . Before I enter any number in E5 and C5, '0' appears in G5. How do I prevent the '0' from showing in cell G5 ? Cheers |
bk T (215) | ||
| 254361 | 2004-07-22 13:45:00 | Im not an Excel expert, but I'd say you have had a formula in there previously, which without values in the other two cells will give the null value '0'. | jerry_23 (3745) | ||
| 254362 | 2004-07-22 13:52:00 | I think I understand your question .. and if I'm right, you can't have nothing in G5 if you have made a formula there. | jerry_23 (3745) | ||
| 254363 | 2004-07-22 20:40:00 | Easy way: Format menu, cells. Format tab, choose custom and then use # (for no decimal places) or #.## for two decimal places. Gets ugly with comma separators and decimal points but the # means display nothing if zero. Hard way: =if(E5="",if(C5="","",E5-C5),C5-E5) robo. |
robo (205) | ||
| 254364 | 2004-07-22 22:23:00 | further to robo's suggestion, you can format it as #,### or #,###.## - the comma will only show for numbers over 999 (or smaller than -999) - but the decimal place will always show for the #.## and #,###.## formats - even if there are no numbers in the other cells. | andrew93 (249) | ||
| 254365 | 2004-07-22 22:37:00 | Yes, it's that damn decimal that drives me nuts. BUT: If you use the following custom format: #,###.#;-#,###.#;# It formats positive numbers with a decimal (if required); negative numbers with a decimal; and the last # after the second semi-colon means no decimal when the number is zero (neat, eh?). I forget about this, but it was bubbling around in the back of my head. robo. |
robo (205) | ||
| 254366 | 2004-07-22 22:41:00 | Thanks everyone for your contribution. That's Great! :) | bk T (215) | ||
| 254367 | 2004-07-22 22:41:00 | > I forget about this, but it was bubbling around in > the back of my head. I know - it's a bit early in the morning - can't think, brain still waking :D |
andrew93 (249) | ||
| 254368 | 2004-07-22 23:30:00 | > I know - it's a bit early in the morning - can't think, brain still waking > :D Coffee is supposed to fix that. ;-) :D |
Susan B (19) | ||
| 254369 | 2004-07-23 00:47:00 | =IF(OR(ISNUMBER(D1),ISNUMBER(E1)),E1-C1,"") Cell formating is optional Many companies would rather lose you as a customer than fix the problem, or even admit that a problem exists - Anonymous |
Merlin (503) | ||
| 1 2 | |||||