Blank cells

[Guest]

Hope someone can help me here. I have 2 situations that are connected to the same project.

1. I doing a spreadsheet for Installers... cells B6 to B15 lists the individual Jobs; Cells D6 - D15 to O6 - O15 will contain values for each month.
The value in Column P is the average of D - O (=AVERAGE(D__:O__). this works fine.

However, (for example) if there is no "job" to record in Row 13, cells D13 to O13 will record 0 (zero). How do I get Cell P13 not to show anything in that cell
(right now, it has #DIV/0!).

2. Also, in Column Q, I have this formula:
=IF(P6>3.5,"NONE",IF(P6>=3,"INSPECTION",IF(P6<3,"WITHHOLD")))

in other words, the Q cell will show the words NONE, INSPECTION or WITHOLD, based on what is in the P cell.

If I have nothing to record, as in the first situation, how do I incorporate the message that if there is no value in P__ cell, leave Q__ cell blank.

These two things are driving me nuts...

 

[Dave R.]

For #1- there is argument over the best way to do it, since you want to be
aware of errors in your data. But this is what I would use :|


=+IF(ISERROR(AVERAGE(D1:O1)),"",AVERAGE(D1:O1))

For #2,

=+IF(ISBLANK(P1),"",IF(P6>3.5,"NONE",IF(P6>=3,"INSPECTION",IF(P6<3,"WITHHOLD
"))))

Hope someone can help me here. I have 2 situations that are connected to the same project.

1. I doing a spreadsheet for Installers... cells B6 to B15 lists the

individual Jobs; Cells D6 - D15 to O6 - O15 will contain values for each
month.

The value in Column P is the average of D - O (=AVERAGE(D__:O__). this works fine.

However, (for example) if there is no "job" to record in Row 13, cells

D13 to O13 will record 0 (zero). How do I get Cell P13 not to show anything
in that cell

(right now, it has #DIV/0!).

2. Also, in Column Q, I have this formula:
=IF(P6>3.5,"NONE",IF(P6>=3,"INSPECTION",IF(P6<3,"WITHHOLD")))

in other words, the Q cell will show the words NONE, INSPECTION or

WITHOLD, based on what is in the P cell.

If I have nothing to record, as in the first situation, how do I

incorporate the message that if there is no value in P__ cell, leave Q__
cell blank.

 

[Dave R.]

make the last one point to P6 not P1..

For #1- there is argument over the best way to do it, since you want to be
aware of errors in your data. But this is what I would use :|


=+IF(ISERROR(AVERAGE(D1:O1)),"",AVERAGE(D1:O1))

For #2,

=+IF(ISBLANK(P1),"",IF(P6>3.5,"NONE",IF(P6>=3,"INSPECTION",IF(P6<3,"WITHHOLD
"))))




to
the same project.
individual Jobs; Cells D6 - D15 to O6 - O15 will contain values for each
month. this
works fine. cells
D13 to O13 will record 0 (zero). How do I get Cell P13 not to show anything
in that cell
WITHOLD, based on what is in the P cell.
incorporate the message that if there is no value in P__ cell, leave Q__
cell blank.

 

[mare]

This is to Dave (my "reply (e-mail") is not working)...

The first situation is working absolutely fine... Thank
you.


But in the second situation, the target cell (Q6) shows
the value "NONE" (it should be blank), because there is no
value in P6. I typed in exactly as you instructed (I did
get your second message about changing P1 to P6). I made
sure that there were no spaces in the formula and that I
had the correct number of parentheses.

Is there a certain way that cells D6:O6 should be
formatted? There is nothing typed in them, but they are
formatted as "Number", and in "Option", the "Zero Values"
has be deselected in the View Tab.

 

[Dave R.]

Hi, the formatting of D6:O6 should only come into play once it passes the
first IF statement, where if there were something in P6, it would evaluate
the number in P6. From what you are saying, it thinks there is a value in
P6.

=+IF(ISNUMBER(P6),IF(P6>3.5,"NONE",IF(P6>=3,"INSPECTION",IF(P6<3,"WITHHOLD",
""))),"")

that should work, and is probably what I should have done earlier. Also I
noticed that you will get "inspection" only if the value is between 3 and
3.5.

Now it sees if theres a number there. any text will return blank, and empty
will return blank.

Dave

 

[mare]

To Dave R:
Thank you so much! IT WORKS!!! It's exactly what I was
looking for!