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

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

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

"Kevin B" wrote in message
...
"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

Try:

=N(U10)/1000000

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"GKS" wrote in message
...

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