+ Reply to Thread
Results 1 to 3 of 3

Copy-pasted or typed zero but the result will turn out negative

  1. #1
    Registered User
    Join Date
    05-20-2022
    Location
    Philippines
    MS-Off Ver
    Office 365
    Posts
    2

    Copy-pasted or typed zero but the result will turn out negative

    Hello guys! I would like to ask a simple question that has stumped me for 2 years in using Excel.

    I've been using a simple formula, just a summation, to calculate the payroll. I make sure that there are only two decimal points. However, recently it has come back again. Whenever I copy-pasted or typed zero on an empty cell, the result will return to (0.00) which is a negative number.

    Attached herewith is the sample workbook.

    I could ignore it but it irks my eyes and I am getting distracted

    If I could, I want to know the underlying problem. I know I can use the round formula but that will be my last resort
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Copy-pasted or typed zero but the result will turn out negative

    Formatting column AZ to be scientific, I see values on the order of -1E-13 or closer to 0, which suggests to me floating point error. Excel, like any other programming language that uses floating point data types, cannot exactly store most decimal values, so you get errors when you try to add them up. More than you want to know about floating point arithmetic and errors: https://www.excelforum.com/groups/ma...nd-errors.html

    There is no fix for this. We as programmers just need to be aware of it and try to minimize it. In most cases, appropriate rounding functions [like =ROUND(current formula in AZ,2)] are usually adequate in Excel.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    05-20-2022
    Location
    Philippines
    MS-Off Ver
    Office 365
    Posts
    2

    Re: Copy-pasted or typed zero but the result will turn out negative

    Thank you for the insight! I guess I'll have to add the round function to the formulas to prevent these.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] VBA code if value is typed or pasted in a cell then current date is returned
    By Roux11 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 05-29-2020, 02:12 AM
  2. Run macro when data based on cell value that is pasted and not typed in
    By originalJ in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-02-2016, 01:56 PM
  3. Can Macros be set to automatically run when something is pasted or typed into a cell?
    By Lylestyle89 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-25-2013, 09:06 PM
  4. Replies: 5
    Last Post: 02-23-2007, 12:11 PM
  5. [SOLVED] Can numbers be typed to automatically be negative in cells?
    By finnplan in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 12-08-2005, 06:30 PM
  6. [SOLVED] can I turn off the memory carry-over from previously typed cells
    By Mpski5 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-17-2005, 03:06 PM
  7. Help: Pasted red numbers turn black
    By Al in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 01-19-2005, 08:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1