2007/4/12, Johnny Andersson <[EMAIL PROTECTED]>:
2007/4/11, Dan Cox <[EMAIL PROTECTED]>: > > Here's one that I am sure will get plenty of replies: > > I am setting up a spread sheet to calculate payments, balance owed, etc, > and I have 25 rows that can have data input (just to keep it all on one > sheet). How do I keep the calculating formula, but make the cells stay blank > (not show $0.00) if I do not put name or some other identifier into that > row. In other words, how do I get only the rows that need to have calculated > to show calculations, and have the remainder stay blank, but still ready to > accept data. I do not want to hide the rows, because I am not the one who > will use this spreadsheet, and that has already caused me messes in the > past. Thank you in advance for the replies. > > Dan > > > Sincerely, > > Dan Cox, CEO > Hi again! I experimented a bit with currency formatting and I came up with the following solution, so that $0.00 will be shown as blanc: Right click the cell → Format Cells… → Click "Numbers" tab if it's not already shown → Select "Currency" in the "Category" field → Select something that looks good in the "Format" field, such as " -$1,234.00" → Add ";#" to the end of what showed up in the "Format code" field. It may look like this, in your case: [$$-409]#,##0.00;[RED]-[$$-409]#,##0.00;# It works like this: # means that a numerical character is shown if it's not zero. 0 means that a numerical character will always be shown. There are also two semi colons, and they work like this: Code1;Code2;Code3 If the value of the cell > 0, Code1 will be applied. If the value of the cell < 0, Code2 will be applied. If the value of the cell = 0, Code3 will be applied. In this case Code1 = [$$-409]#,##0.00 Code2 = [RED]-[$$-409]#,##0.00 Code3 = # So if the cell value = 0, then Code3 will be applied which is # which means that nothing is shown. The [$$-409] is probably just to get the right currency symbol and things like that. This will also work: [$$-409]#,##0.00;[RED]-[$$-409]#,##0.00;"" [$$-409]#,##0.00;[RED]-[$$-409]#,##0.00;@ This is fun, because I didn't think of this solution before! I always did the IF thing! This could save me a lot of work in the future! Thanks to you! For normal two decimal numbers, with no special formatting, the code is as easy as "0.00;-0.00;#" or "#,##0.00;-#,##0.00;#"! I also tried this, which ALSO worked: B12: Hello C12: =B12 C12 format: 0;0;# (#;#;# and @;@;@ etc. will also work, because the important thing is what's after the second semi colon) C12 shows: Hello Delete the contents of B12, then C12 now shows NOTHING! Just as it should be (in most cases anyway)! Before formatting it showed 0 when B12 was empty! Johnny Andersson
I just forgot to mention that this method doesn't work if you actually WANT $0.00 to be shown if the result actually is 0. In that case you will need the IF thing after all, I guess. Example 1, the formatting method: [Empty] → [Empty] 0 → [Empty] 1 → $1.00 Example 2, the IF method: [Empty] → [Empty] 0 → $0.00 1 → $1.00 If you want the results of example 2, I am afraid that you need the IF statement, sorry. Johnny Andersson
