+ Reply to Thread
Results 1 to 5 of 5

Formula?

  1. #1
    Registered User
    Join Date
    03-25-2006
    Location
    UK
    Posts
    7

    Formula?

    Can anyone help? I am new to excel but I am trying very hard to learn ASAP, The problem is that I have a column of numbers that go on for thousands of rows. This data will change weekly as it is imported in from another program. using the delimiter function but the column will stay the same, in this example it is column U. Below is a snapshot of some of the rows in U. I used the formula U##/1000000 to give the answers in the V column then I simply hide the U column, but as you can see when the imported data is all zeros it returns a value message. Is there any way that in the simple formula I have used I could add something that basically says divide amount in cell by 1000000 but if the data in the cell = 0 then return 0

    U V
    0,000000 #VALUE!
    0,000000 #VALUE!
    972,000,000 972
    342,000,000 342
    266,000,000 266
    12,570,000,000 12570
    1,920,000,000 1920
    8,640,000,000 8640
    5,400,000,000 5400

    Thanks in anticipation of any help

  2. #2
    Kevin B
    Guest

    RE: Formula?

    You can use an IF formula similar to the following:

    =IF(A1=0,0,A1/1000000)
    --
    Kevin Backmann


    "GKS" wrote:

    >
    > Can anyone help? I am new to excel but I am trying very hard to learn
    > ASAP, The problem is that I have a column of numbers that go on for
    > thousands of rows. This data will change weekly as it is imported in
    > from another program. using the delimiter function but the column will
    > stay the same, in this example it is column U. Below is a snapshot of
    > some of the rows in U. I used the formula U##/1000000 to give the
    > answers in the V column then I simply hide the U column, but as you can
    > see when the imported data is all zeros it returns a value message. Is
    > there any way that in the simple formula I have used I could add
    > something that basically says divide amount in cell by 1000000 but if
    > the data in the cell = 0 then return 0
    >
    > U V
    > 0,000000 #VALUE!
    > 0,000000 #VALUE!
    > 972,000,000 972
    > 342,000,000 342
    > 266,000,000 266
    > 12,570,000,000 12570
    > 1,920,000,000 1920
    > 8,640,000,000 8640
    > 5,400,000,000 5400
    >
    > Thanks in anticipation of any help
    >
    >
    > --
    > GKS
    > ------------------------------------------------------------------------
    > GKS's Profile: http://www.excelforum.com/member.php...o&userid=32815
    > View this thread: http://www.excelforum.com/showthread...hreadid=534528
    >
    >


  3. #3
    Toppers
    Guest

    RE: Formula?

    Use:

    =If(U1=0,0,U1/1000000)

    HTH

    "GKS" wrote:

    >
    > Can anyone help? I am new to excel but I am trying very hard to learn
    > ASAP, The problem is that I have a column of numbers that go on for
    > thousands of rows. This data will change weekly as it is imported in
    > from another program. using the delimiter function but the column will
    > stay the same, in this example it is column U. Below is a snapshot of
    > some of the rows in U. I used the formula U##/1000000 to give the
    > answers in the V column then I simply hide the U column, but as you can
    > see when the imported data is all zeros it returns a value message. Is
    > there any way that in the simple formula I have used I could add
    > something that basically says divide amount in cell by 1000000 but if
    > the data in the cell = 0 then return 0
    >
    > U V
    > 0,000000 #VALUE!
    > 0,000000 #VALUE!
    > 972,000,000 972
    > 342,000,000 342
    > 266,000,000 266
    > 12,570,000,000 12570
    > 1,920,000,000 1920
    > 8,640,000,000 8640
    > 5,400,000,000 5400
    >
    > Thanks in anticipation of any help
    >
    >
    > --
    > GKS
    > ------------------------------------------------------------------------
    > GKS's Profile: http://www.excelforum.com/member.php...o&userid=32815
    > View this thread: http://www.excelforum.com/showthread...hreadid=534528
    >
    >


  4. #4
    David Biddulph
    Guest

    Re: Formula?

    "Kevin B" <kbackmann@sbcglobal.net.spamBgone> wrote in message
    news:8B0EB0D0-A8E9-4235-937F-45296029028C@microsoft.com...
    > "GKS" wrote:


    >> Can anyone help? I am new to excel but I am trying very hard to learn
    >> ASAP, The problem is that I have a column of numbers that go on for
    >> thousands of rows. This data will change weekly as it is imported in
    >> from another program. using the delimiter function but the column will
    >> stay the same, in this example it is column U. Below is a snapshot of
    >> some of the rows in U. I used the formula U##/1000000 to give the
    >> answers in the V column then I simply hide the U column, but as you can
    >> see when the imported data is all zeros it returns a value message. Is
    >> there any way that in the simple formula I have used I could add
    >> something that basically says divide amount in cell by 1000000 but if
    >> the data in the cell = 0 then return 0
    >>
    >> U V
    >> 0,000000 #VALUE!
    >> 0,000000 #VALUE!
    >> 972,000,000 972
    >> 342,000,000 342
    >> 266,000,000 266
    >> 12,570,000,000 12570
    >> 1,920,000,000 1920
    >> 8,640,000,000 8640
    >> 5,400,000,000 5400


    > You can use an IF formula similar to the following:
    >
    > =IF(A1=0,0,A1/1000000)


    Have you tried it with GKS's data? There should, of course, be no problem
    in dividing zero by a million (and therefore wouldn't need the IF test), but
    the #VALUE problem comes about because 0,000000 isn't treated as a number of
    zero, but as a text string.

    You could, instead, use
    =IF(ISNUMBER(A1),A1/1000000,0)
    --
    David Biddulph



  5. #5
    Sandy Mann
    Guest

    Re: Formula?

    Try:

    =N(U10)/1000000

    --
    HTH

    Sandy
    In Perth, the ancient capital of Scotland

    sandymann2@mailinator.com
    Replace@mailinator.com with @tiscali.co.uk


    "GKS" <GKS.26kcxn_1145537700.906@excelforum-nospam.com> wrote in message
    news:GKS.26kcxn_1145537700.906@excelforum-nospam.com...
    >
    > Can anyone help? I am new to excel but I am trying very hard to learn
    > ASAP, The problem is that I have a column of numbers that go on for
    > thousands of rows. This data will change weekly as it is imported in
    > from another program. using the delimiter function but the column will
    > stay the same, in this example it is column U. Below is a snapshot of
    > some of the rows in U. I used the formula U##/1000000 to give the
    > answers in the V column then I simply hide the U column, but as you can
    > see when the imported data is all zeros it returns a value message. Is
    > there any way that in the simple formula I have used I could add
    > something that basically says divide amount in cell by 1000000 but if
    > the data in the cell = 0 then return 0
    >
    > U V
    > 0,000000 #VALUE!
    > 0,000000 #VALUE!
    > 972,000,000 972
    > 342,000,000 342
    > 266,000,000 266
    > 12,570,000,000 12570
    > 1,920,000,000 1920
    > 8,640,000,000 8640
    > 5,400,000,000 5400
    >
    > Thanks in anticipation of any help
    >
    >
    > --
    > GKS
    > ------------------------------------------------------------------------
    > GKS's Profile:
    > http://www.excelforum.com/member.php...o&userid=32815
    > View this thread: http://www.excelforum.com/showthread...hreadid=534528
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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