| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Blanks treated as zeroes in a numeric field
How can I stop Excel from treating blanks as zeroes in a numeric field?
|
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Text treated as a numeric value? | Excel Discussion (Misc queries) | |||
| Leading zeroes in ZIP field | Excel Discussion (Misc queries) | |||
| Count IF excluding blanks or zeroes | Excel Worksheet Functions | |||
| excel .txt to .cvs, lose trailing zeroes in numeric field | Excel Discussion (Misc queries) | |||
| Hiding rows containing zeroes or blanks in pivot tables? | Excel Discussion (Misc queries) | |||
Linear Mode
