+ Reply to Thread
Results 1 to 10 of 10

Converting negative numbers with negative decimal numbers? (-32,-768)

  1. #1
    Registered User
    Join Date
    04-19-2016
    Location
    Denmark
    MS-Off Ver
    Office 2014
    Posts
    5

    Converting negative numbers with negative decimal numbers? (-32,-768)

    Hi!

    I have an energy meter (one of those your put in a wallplug, so you can see the energy consumption of an electrical device).

    The logger saves data on a SD-card as a .CSV file, but the kWh counter seems to be locked to 16 bits, since it counts up from zero towards 32,767 kWh,
    then it starts over from -32,768 kWh and counts up to 32,767 kWh again and so on.

    Now i could just add 32,768 too all the negative numbers to get all positive numbers, but it writes the number as "-32.-768".

    So basically my question is: is it possible to convert a number with a negative decimal number, to a normal negative or possitive number in excel?

    I know some VBA programming aswell if anyone has a solution through that.

    I hope my english will do as it is not my first language

    Best Regards
    S. Rafen

  2. #2
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Converting negative numbers with negative decimal numbers? (-32,-768)

    Hm. That seems like either a format issue or the number is being treated as text by Excel. Can you post a sample of the data?
    ____________________________________________
    If this has solved your problem, please edit the thread title to add the word [SOLVED] at the beginning. You can do this by
    -Go to the top of the first post
    -Select Thread Tools
    -Select Mark thread as Solved

    If I have been particularly helpful, please "bump" my reputation by pressing the small star in the lower left corner of my post.

  3. #3
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Converting negative numbers with negative decimal numbers? (-32,-768)

    So when you open the CSV file, you see, within a single cell
    "-32. -768" (without the quotes)? Excel is seeing that as text so , if there is a space between the period and the second negative sign try this formula (it sets -32768 at 0)

    =LEFT(A1,SEARCH(" ",A1)-1)*1000+MID(A1,SEARCH("-",A1,2),10)+32768
    Does that work for you?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Converting negative numbers with negative decimal numbers? (-32,-768)

    What exactly do you see in the cell when you pull the data in?
    Can you show us a sample?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Registered User
    Join Date
    04-19-2016
    Location
    Denmark
    MS-Off Ver
    Office 2014
    Posts
    5

    Re: Converting negative numbers with negative decimal numbers? (-32,-768)

    I see " -32.-768" in the cell without quotes, in the .CSV.
    If i try to import it as text to excel it turns it into "=-32-768" without quotes, so "800" in the cell, but "=-32-768" as the formula.

    "=LEFT(A1,SEARCH(" ",A1)-1)*1000+MID(A1,SEARCH("-",A1,2),10)+32768" Did not work.

    And there is no space in between the period and the negative sign.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Converting negative numbers with negative decimal numbers? (-32,-768)

    so it actually imports it as a formula?

  7. #7
    Registered User
    Join Date
    04-19-2016
    Location
    Denmark
    MS-Off Ver
    Office 2014
    Posts
    5

    Re: Converting negative numbers with negative decimal numbers? (-32,-768)

    Yes it does.

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Converting negative numbers with negative decimal numbers? (-32,-768)

    Interesting, never see it do that before. Can you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

  9. #9
    Registered User
    Join Date
    04-19-2016
    Location
    Denmark
    MS-Off Ver
    Office 2014
    Posts
    5

    Re: Converting negative numbers with negative decimal numbers? (-32,-768)

    For some reason i can't use the attachment feature on the forum, so i uploaded them to Uptobox instead.

    Sample:
    Forum.xlsx - 16 KB

    Full raw .CSV:
    PD160323.CSV - 5.5 MB

    Periods are decimal separators and commas (if any) thousand separators.
    Last edited by Rafen90; 04-20-2016 at 02:43 AM.

  10. #10
    Registered User
    Join Date
    04-19-2016
    Location
    Denmark
    MS-Off Ver
    Office 2014
    Posts
    5

    Re: Converting negative numbers with negative decimal numbers? (-32,-768)

    And for some reason my laptop here, on which i made the sample from, does not import the data as a formula as my desktop did last night.... but yeah

+ 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. decimal powers of negative numbers
    By samartil in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-04-2013, 12:57 AM
  2. Converting Negative Numbers Into Dates
    By artoni in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-02-2009, 09:17 PM
  3. Converting Negative Numbers to 0's
    By DangerMouse in forum Excel General
    Replies: 3
    Last Post: 01-12-2007, 06:19 AM
  4. Converting positive numbers to negative numbers
    By Treg67 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-17-2006, 11:00 AM
  5. converting text to negative numbers!
    By via135 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-05-2006, 02:29 AM
  6. Replies: 0
    Last Post: 08-23-2005, 03:59 AM
  7. [SOLVED] Converting negative numbers in a range of cells to zero
    By Dede in forum Excel General
    Replies: 3
    Last Post: 01-14-2005, 03:06 PM
  8. Converting negative numbers
    By crparedes in forum Excel General
    Replies: 2
    Last Post: 01-10-2005, 04:46 PM

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