#error to be removed

M

Majid

Hello Dear:
I Have customized a column that has the formula for
mutipling the values from the other columns and gives me a
subtotal and that is great. What is disturbing is that
the # error is appearing on all other subtasks that does
not have values and I have no intention to put values on
the subtasks at all. I did try to change the column
property from Number to text and in this case the # error
does not show which is great however my formula does not
add the text columns!
Please advise
Regards,
Majid
 
M

Mike Glen

Hi Majid,

The ### usually mean that the column is not wide enough to show the data.
Move your cursor in the column heading to the right margin of the column
until you see a left/right arrow, click and drag to the right to open it
up.


Mike Glen
Project MVP
 
M

Majid

Hi Mike,
Sorry it is # error not ######
Let me explain in more detail. my customized columns are
baseds on numbers. If I change the columns property to
text then there is no # error or zero shown in non value
entered columns however the formula does not work for the
text column.
Please advise
 
M

Mike Glen

In that case, the # means mis-matched data. Maybe you'd like to show us the
calculations you're using.


Mike Glen
Project MVP
 
J

JulieS

Hi Majid,

You mention that you are seeing zeros shown in non value
entered columns. If the math you attempting to causes
the #Error message because of the zeros (for example
dividing by zero) try the following formula for the
calculated field:

IIf([Number1]=0 Or [Number2]=0,0,[Number1]/[Number2])

Replace the [Number1] and [Number2] field references with
your fields and replace the last part of the formula with
the formula that you need calculated.

Hope this helps.

Julie
 
M

Majid

Hi mike
Your are right, my formula had a glitch and that was
mismatching the column ID. Some of them had text ID and
some had the number ID. I fixed that already and now the
rows that have no value are written as zero which is
correct but I do not want to see these zeros. Is there
any way to hide them? One solution to this was to assign
the text ID to the colums so that eliminating the zeros on
non value rows but then my formula is mismatching because
my formula is as follows:
([Number1]*[Number2])*([text1]+[text2])
Please advise
Majid

My formual
 
M

Mike Glen

I'm not sure you can eliminnate the zero in a number field. I suggest you
follow Julie's suggestion and use an IIF statement. Assume you have 3
number fields, 1 and 2 holding data and Number3 the result of a formula
manipulating the data in 1 and 2. then insert a Text1 field and customise it
with the formula IIf([Number3]=0,"",[Number3]).

Mike Glen
Project MVP



Hi mike
Your are right, my formula had a glitch and that was
mismatching the column ID. Some of them had text ID and
some had the number ID. I fixed that already and now the
rows that have no value are written as zero which is
correct but I do not want to see these zeros. Is there
any way to hide them? One solution to this was to assign
the text ID to the colums so that eliminating the zeros on
non value rows but then my formula is mismatching because
my formula is as follows:
([Number1]*[Number2])*([text1]+[text2])
Please advise
Majid

My formual
-----Original Message-----
In that case, the # means mis-matched data. Maybe you'd like to
show us the calculations you're using.


Mike Glen
Project MVP
Click to expand...
Click to expand...
 
J

JackD

Mike is correct. There is no way to have a number field be blank. It is
always 0 or some other value.
If you want a blank field then you must use a text field.

-Jack

Mike Glen said:
I'm not sure you can eliminnate the zero in a number field. I suggest you
follow Julie's suggestion and use an IIF statement. Assume you have 3
number fields, 1 and 2 holding data and Number3 the result of a formula
manipulating the data in 1 and 2. then insert a Text1 field and customise it
with the formula IIf([Number3]=0,"",[Number3]).

Mike Glen
Project MVP



Hi mike
Your are right, my formula had a glitch and that was
mismatching the column ID. Some of them had text ID and
some had the number ID. I fixed that already and now the
rows that have no value are written as zero which is
correct but I do not want to see these zeros. Is there
any way to hide them? One solution to this was to assign
the text ID to the colums so that eliminating the zeros on
non value rows but then my formula is mismatching because
my formula is as follows:
([Number1]*[Number2])*([text1]+[text2])
Please advise
Majid

My formual
-----Original Message-----
In that case, the # means mis-matched data. Maybe you'd like to
show us the calculations you're using.


Mike Glen
Project MVP




Majid wrote:
Hi Mike,
Sorry it is # error not ######
Let me explain in more detail. my customized columns are
baseds on numbers. If I change the columns property to
text then there is no # error or zero shown in non value
entered columns however the formula does not work for the
text column.
Please advise
-----Original Message-----
Hi Majid,

The ### usually mean that the column is not wide enough to show the
data. Move your cursor in the column heading to the right margin of
the column until you see a left/right arrow, click and drag to the
right to open it up.


Mike Glen
Project MVP



Majid wrote:
Hello Dear:
I Have customized a column that has the formula for
mutipling the values from the other columns and gives me a
subtotal and that is great. What is disturbing is that
the # error is appearing on all other subtasks that does
not have values and I have no intention to put values on
the subtasks at all. I did try to change the column
property from Number to text and in this case the # error
does not show which is great however my formula does not
add the text columns!
Please advise
Regards,
Majid
Click to expand...
Click to expand...
Click to expand...
 
M

Majid

Thank you all. I did work with IIF statement
Regards,
Majid
-----Original Message-----
Mike is correct. There is no way to have a number field be blank. It is
always 0 or some other value.
If you want a blank field then you must use a text field.

-Jack

Mike Glen said:
I'm not sure you can eliminnate the zero in a number field. I suggest you
follow Julie's suggestion and use an IIF statement. Assume you have 3
number fields, 1 and 2 holding data and Number3 the result of a formula
manipulating the data in 1 and 2. then insert a Text1
Click to expand...
field and customise
it
with the formula IIf([Number3]=0,"",[Number3]).

Mike Glen
Project MVP



Hi mike
Your are right, my formula had a glitch and that was
mismatching the column ID. Some of them had text ID and
some had the number ID. I fixed that already and now the
rows that have no value are written as zero which is
correct but I do not want to see these zeros. Is there
any way to hide them? One solution to this was to assign
the text ID to the colums so that eliminating the zeros on
non value rows but then my formula is mismatching because
my formula is as follows:
([Number1]*[Number2])*([text1]+[text2])
Please advise
Majid

My formual
-----Original Message-----
In that case, the # means mis-matched data. Maybe you'd like to
show us the calculations you're using.


Mike Glen
Project MVP




Majid wrote:
Hi Mike,
Sorry it is # error not ######
Let me explain in more detail. my customized columns are
baseds on numbers. If I change the columns property to
text then there is no # error or zero shown in non value
entered columns however the formula does not work for the
text column.
Please advise
-----Original Message-----
Hi Majid,

The ### usually mean that the column is not wide enough to show the
data. Move your cursor in the column heading to the right margin of
the column until you see a left/right arrow, click and drag to the
right to open it up.


Mike Glen
Project MVP



Majid wrote:
Hello Dear:
I Have customized a column that has the formula for
mutipling the values from the other columns and gives me a
subtotal and that is great. What is disturbing is that
the # error is appearing on all other subtasks that does
not have values and I have no intention to put values on
the subtasks at all. I did try to change the column
property from Number to text and in this case the # error
does not show which is great however my formula does not
add the text columns!
Please advise
Regards,
Majid
Click to expand...
Click to expand...


.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Weigh tasks 0
Setting column width in a table with a macro is not working 0
Dealing with the error: unable to filter on excel hc home school 0
Trying to remove error from conflicting code 0
Copy addition formula of non-consecutive horizontal cells 2
Working outlook via vba code with xlsx - error 0
MS Project Import Error Row 1 Column 5 2
Calculate the median value before a specified list 0

Top