6

A colleague and I encountered a behavior in Excel which isn't clear to us.

Background: We have a tool which converts an Excel sheet into a table format. The tool calculates the formulas which are in excel and replaces variables inside it with specific values. The excel tool is used by one of our customers who use values like (8) or (247). These Value are automatically translated by excel to -8 or -247.

Question: I saw that many people want to display negative numbers in parentheses. But why would Excel change values in parentheses to a negative number?

I know that I could simply change the cell config to text and this would solve the problem but I wonder if there is a reason for the behavior, since there seems to be no mathematical reason for this.

Ṃųỻịgǻňạcểơửṩ
  • 2,517
  • 8
  • 22
  • 33
Bongo
  • 2,933
  • 5
  • 36
  • 67
  • 4
    If you want to display them in parentheses, since that is your "native" notion of negative numbers, you probably want to enter them the same way. – Erich Kitzmueller Apr 15 '15 at 11:08
  • 2
    @ammoQ A quick google with, notion, negative number and parentheses got me the answer. I didn't know that there is a different way than a minus to display negativ numbers. Yay learned something . But you wouldn't do it in math or am i wrong ? – Bongo Apr 15 '15 at 11:13
  • 6
    As far as I know, this is typically done in accounting. In math, it would be a bad idea, for all the other meanings of parentheses. – Erich Kitzmueller Apr 15 '15 at 11:14
  • 2
    No, in pure mathematics that's not a valid notation for negative numbers. – m0skit0 Apr 15 '15 at 11:14
  • 2
    Accountants like to use () for negatives as they show up better in photocopied documents. Remember the old days when photocopies were terrible? – Bathsheba Apr 15 '15 at 11:19
  • 2
    You've been around on SO for a while; why are you posting this non-programming-related question here? – Jean-François Corbett Apr 15 '15 at 11:24
  • 1
    @Jean-FrançoisCorbett should I post a part of my C# excel addin where this fails? I narrowed it down to be an excel problem and wanted to know why excel behaves like this so that I can work on a solution and maybe error warnings in the c# excel addin. You are right, i am around on SO for a while and I regard this as a programming related question. Even though, I have to admit that the question is only related and doesn't give an opportunity to program anything to answer the question. Feel free to delete the question but it gives me and a collegue the information needed to alter the programcode – Bongo Apr 15 '15 at 11:36
  • 4
    Now I understand, thanks. As is stands, though, the question, if you look at it, isn't programming-related. – Jean-François Corbett Apr 15 '15 at 11:59
  • 6
    Excel is overly helpful in the assumptions it makes. We have part numbers that often take a form like `1E123` and excel automagically, and ever-so-helpfully, converts those to scientific notation. It will drop leading 0s from anything that looks like a number. It will convert things to dates that aren't dates. And, as you have found, because of traditional use of parentheses to show negative values in Finance, it's helpfully converting for you. – JNevill Apr 15 '15 at 13:18

3 Answers3

1

Its simply the different format of cells you are bringing the "values from" and "pasting to". ..... numbers with parentheses are in cells with "accounting" format and negatives are stored in general or standard number formated cells. To resolve you can change the format of destination cells to accounting using cell formatting as number>accounting.

Asif DanS
  • 79
  • 1
  • 2
1

To answer the why, it's because accountants put negative numbers in brackets for readability

Unfortunately, this is one of the excel feature/bugs that helps some folks and frustrates others. When opening a file or pasting content, excel will immediately and always try to parse any values into formats it deems appropriate, which can mess up data like:

  • Zip Codes / Tel. # → Numeric: 05401 → 5401
  • Fractions → Dates: 11/20 → Nov, 20th YYYY
  • Std. Errors → Negative Numbers: (0.1) → -0.1

For some workarounds , see Stop Excel from automatically converting certain text values to dates

Once the file is open/pasted, the damage is already done. At that point, your best bet is:

  • Updating the field and displaying as text (appending with ') to prevent re-casting
  • Formatting the field if the operation wasn't lossy and is just presenting the info differently
  • Running a clean if/else to pad or other convert your data based on the identified errors

Specific to displaying values back in parens, if excel is converting them and treating them like negative numbers (which may or may not be the appropriate way to actually store the data), you can apply a different format to positive and negative numbers to wrap back in parens.

Format Cells

KyleMit
  • 30,350
  • 66
  • 462
  • 664
1

It is standard practice to write negative values as numbers in parentheses, especially in accounting. This makes negative values stand out much more than a simple negative hyphen; compare -1 and (1).

Excel is a tool very commonly used by accountants and supports accountant-style spreadsheets. Therefore, entering (100) means having a value of -100, even if there is no minus hyphen!

Here is a fun fact, if you enter (-10), Excel will treat it as normal text.

Ṃųỻịgǻňạcểơửṩ
  • 2,517
  • 8
  • 22
  • 33
  • 1
    Interesting. Do you know any other field where parentheses are used in this way? (Personally, I would like to switch this feature/bug off but I doubt this will ever be possible.) – lebatsnok Apr 04 '20 at 20:05
  • @lebatsnok Accounting and financial accounts use parentheses to indicate negatives. That makes negatives stand out more while remaining unambiguous in that context. Excel is commonly used in accounting. – Ṃųỻịgǻňạcểơửṩ Apr 04 '20 at 22:27
  • 1
    Yes, thanks but my question was about all *other* fields besides accounting. Parentheses are a way to make a number stand out, yeah, sure. But that sounds like a solution from 1980s -- a cell with a pink background, for instance, serves that purpose so much better. So if Excel is not meant for just accountants from 1980s, one should be able to switch this feature/bug off. And if it is meant for accountants and nobody else, it should be advertised as such. (I'm personally ok without excel -- but I would prefer its convenience without the irritating bug/features.) – lebatsnok Apr 21 '20 at 17:51
  • @lebatsnok In my impression, number in parentheses can be used when a given statistic or data is a single negative number, such as the usage data for apps in Task Manager (not sure if that applies for the W10 version). This is in fact the first time I ever saw negative numbers as parenthesized numbers, and is usually used when the rate of change is negative; for example a (30%) memory usage change. – Ṃųỻịgǻňạcểơửṩ Apr 21 '20 at 20:10