G
Guest
How can I stop Excel from treating blanks as zeroes in a numeric field?
R
RagDyeR
Depends on what and how you're using them.
Care to elaborate more, with examples?
Care to elaborate more, with examples?
B
Bernard Liengme
In what respect? Give example
best wishes
best wishes
G
Guest
You can do something like this
=if(isblank(A1),na(),a1)
=if(isblank(A1),na(),a1)
G
Guest
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.
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.
G
Guest
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.
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.
R
RagDyeR
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!
=====================================================
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.
=A1-1
Then try this:
=If(A1,A1-1,"")
--
HTH,
RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================
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.
B
Bernard Liengme
Let's say the numbers are in A1:B10
On C1 enter =IF(A1>0,A1-1,"")
Copy to D1 and down to D10
If you do not need the original data:
Select C1
10; use COPY
With the range still selected use Edit | Pate Special and specify Values
Now D110 has numbers (and Blanks) not formulas
You may now delete columns A and B
best wishes
On C1 enter =IF(A1>0,A1-1,"")
Copy to D1 and down to D10
If you do not need the original data:
Select C1
10; use COPYWith the range still selected use Edit | Pate Special and specify Values
Now D1
10 has numbers (and Blanks) not formulasYou may now delete columns A and B
best wishes
G
Guest
Thanks for your help. Problem is, a -1 is a legitmate value if the numeric
value is 0.000.
value is 0.000.
G
Guest
Thanks for your help. Problem is, a -1 is a legitmate value if the numeric
value is 0.000.
value is 0.000.
P
Peo Sjoblom
Just use
=IF(A1="","",A1-1)
--
Regards,
Peo Sjoblom
=IF(A1="","",A1-1)
--
Regards,
Peo Sjoblom
G
Guest
Yeah, that works, thank you ! ! !
Peo Sjoblom said: