Blanks treated as zeroes in a numeric field
 

How can I stop Excel from treating blanks as zeroes in a numeric field?

Blanks treated as zeroes in a numeric field
 

Depends on what and how you're using them.

Care to elaborate more, with examples?
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Bob Aloisi" wrote in message
...
How can I stop Excel from treating blanks as zeroes in a numeric field?

Blanks treated as zeroes in a numeric field
 

In what respect? Give example
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Bob Aloisi" wrote in message
...
How can I stop Excel from treating blanks as zeroes in a numeric field?

Blanks treated as zeroes in a numeric field
 

You can do something like this

=if(isblank(A1),na(),a1)

--
HTH,
Barb Reinhardt



"Bob Aloisi" wrote:

How can I stop Excel from treating blanks as zeroes in a numeric field?

Blanks treated as zeroes in a numeric field
 

I've imported data into Excel and two columns are defined as numeric, 3
decimal places. When using them in a compute, I subtract 1 from the value.
For those that are blank, the result from the compute is -1. I'd prefer the
result be blank as well.

"RagDyeR" wrote:

Depends on what and how you're using them.

Care to elaborate more, with examples?
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Bob Aloisi" wrote in message
...
How can I stop Excel from treating blanks as zeroes in a numeric field?

Blanks treated as zeroes in a numeric field
 

I've imported data into Excel and two columns are defined as numeric, 3
decimal places. When using them in a compute, I subtract 1 from the value.
For those that are blank, the result from the compute is -1. I'd prefer the
result be blank as well.

"Bernard Liengme" wrote:

In what respect? Give example
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Bob Aloisi" wrote in message
...
How can I stop Excel from treating blanks as zeroes in a numeric field?

Blanks treated as zeroes in a numeric field
 

If you're using a formula like:

=A1-1

Then try this:

=If(A1,A1-1,"")

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Bob Aloisi" wrote in message
...
I've imported data into Excel and two columns are defined as numeric, 3
decimal places. When using them in a compute, I subtract 1 from the value.
For those that are blank, the result from the compute is -1. I'd prefer the
result be blank as well.

"RagDyeR" wrote:

Depends on what and how you're using them.

Care to elaborate more, with examples?
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Bob Aloisi" wrote in message
...
How can I stop Excel from treating blanks as zeroes in a numeric field?

Blanks treated as zeroes in a numeric field
 

Let's say the numbers are in A1:B10
On C1 enter =IF(A10,A1-1,"")
Copy to D1 and down to D10

If you do not need the original data:
Select C1:D10; use COPY
With the range still selected use Edit | Pate Special and specify Values
Now D1:D10 has numbers (and Blanks) not formulas
You may now delete columns A and B

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Bob Aloisi" wrote in message
...
I've imported data into Excel and two columns are defined as numeric, 3
decimal places. When using them in a compute, I subtract 1 from the
value.
For those that are blank, the result from the compute is -1. I'd prefer
the
result be blank as well.

"Bernard Liengme" wrote:

In what respect? Give example
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Bob Aloisi" wrote in message
...
How can I stop Excel from treating blanks as zeroes in a numeric field?

Blanks treated as zeroes in a numeric field
 

Thanks for your help. Problem is, a -1 is a legitmate value if the numeric
value is 0.000.

"RagDyeR" wrote:

If you're using a formula like:

=A1-1

Then try this:

=If(A1,A1-1,"")

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Bob Aloisi" wrote in message
...
I've imported data into Excel and two columns are defined as numeric, 3
decimal places. When using them in a compute, I subtract 1 from the value.
For those that are blank, the result from the compute is -1. I'd prefer the
result be blank as well.

"RagDyeR" wrote:

Depends on what and how you're using them.

Care to elaborate more, with examples?
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Bob Aloisi" wrote in message
...
How can I stop Excel from treating blanks as zeroes in a numeric field?

Blanks treated as zeroes in a numeric field
 

Thanks for your help. Problem is, a -1 is a legitmate value if the numeric
value is 0.000.

"Bernard Liengme" wrote:

Let's say the numbers are in A1:B10
On C1 enter =IF(A10,A1-1,"")
Copy to D1 and down to D10

If you do not need the original data:
Select C1:D10; use COPY
With the range still selected use Edit | Pate Special and specify Values
Now D1:D10 has numbers (and Blanks) not formulas
You may now delete columns A and B

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Bob Aloisi" wrote in message
...
I've imported data into Excel and two columns are defined as numeric, 3
decimal places. When using them in a compute, I subtract 1 from the
value.
For those that are blank, the result from the compute is -1. I'd prefer
the
result be blank as well.

"Bernard Liengme" wrote:

In what respect? Give example
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Bob Aloisi" wrote in message
...
How can I stop Excel from treating blanks as zeroes in a numeric field?

Blanks treated as zeroes in a numeric field
 

Just use

=IF(A1="","",A1-1)


--


Regards,


Peo Sjoblom


"Bob Aloisi" wrote in message
...
Thanks for your help. Problem is, a -1 is a legitmate value if the
numeric
value is 0.000.

"Bernard Liengme" wrote:

Let's say the numbers are in A1:B10
On C1 enter =IF(A10,A1-1,"")
Copy to D1 and down to D10

If you do not need the original data:
Select C1:D10; use COPY
With the range still selected use Edit | Pate Special and specify Values
Now D1:D10 has numbers (and Blanks) not formulas
You may now delete columns A and B

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Bob Aloisi" wrote in message
...
I've imported data into Excel and two columns are defined as numeric, 3
decimal places. When using them in a compute, I subtract 1 from the
value.
For those that are blank, the result from the compute is -1. I'd
prefer
the
result be blank as well.

"Bernard Liengme" wrote:

In what respect? Give example
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Bob Aloisi" wrote in message
...
How can I stop Excel from treating blanks as zeroes in a numeric
field?

Blanks treated as zeroes in a numeric field
 

Yeah, that works, thank you ! ! !

"Peo Sjoblom" wrote:

Just use

=IF(A1="","",A1-1)


--


Regards,


Peo Sjoblom


"Bob Aloisi" wrote in message
...
Thanks for your help. Problem is, a -1 is a legitmate value if the
numeric
value is 0.000.

"Bernard Liengme" wrote:

Let's say the numbers are in A1:B10
On C1 enter =IF(A10,A1-1,"")
Copy to D1 and down to D10

If you do not need the original data:
Select C1:D10; use COPY
With the range still selected use Edit | Pate Special and specify Values
Now D1:D10 has numbers (and Blanks) not formulas
You may now delete columns A and B

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Bob Aloisi" wrote in message
...
I've imported data into Excel and two columns are defined as numeric, 3
decimal places. When using them in a compute, I subtract 1 from the
value.
For those that are blank, the result from the compute is -1. I'd
prefer
the
result be blank as well.

"Bernard Liengme" wrote:

In what respect? Give example
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Bob Aloisi" wrote in message
...
How can I stop Excel from treating blanks as zeroes in a numeric
field?