another conditional formatting problem

D

dude3236

Here's the situation:
I have about 1000 cells. The first cell starts at a postive number (i
5000). Each cell afterwards has a value smaller than the previous cel
(ie 5000, 4998, 4990, 4809....). Eventually the number will reach zer
or a negative number.

Once a cell reaches a zero or a negative number, I want all th
remaining cells after it to display nothing (or null or something lik
that).

Here is my formula to do that:
=IF(I23<=0," ",(I23+((I23*($H$14/12))-J23)))

The above is basically saying:
IF(previous cell<=0 THEN display " " ELSE make a calculation to displa
in the current cell)

This works for the first time, but anytime after that, it returns a
error (####).
So the excel sheet would look like this:
5000 4998 4990 4809 -23 #### ####

I know the problem lies in the fact that the IF statement says (I
previous cell <=0). It won't work because the previous cell is #### (a
error).

How can I make it so that the #### is not displayed.

Here are my solutions:
1. Individually conditional format EVERY SINGLE cell. (I prefer not t
since there are 1000+ cells)
2. In each formula, create nested functions to accomplish what I'
trying to do. (this is the ideal solution but I can't seem to figur
out the syntax. I've been trying OR functions with the IF but th
program doesnt seem to like it).

Maybe I could include another statement that says:
IF (previous cell <=0 OR isnull) THEN....

But how do I add the "OR isnull"? I don't know the correct syntax fo
that.

Any help or advice please??
 
B

Bob Phillips

The formula

=IF(I23<=0,"",(I23+((I23*($H$14/12))-J23)))

should work. It suggests that there is something wrong in J23, or H14. most
likely J23 as H14 is used in every row.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
R

radiation_rat

excel puts #### if can't display contents of cell, so increase column width,
also, check what is writen on the formula bar when cell filed with #### is
selected.

If this not help, check formating of the cell, it happens if dates and times
have negative numbers, so change formating to general or number....
 
R

Ron Rosenfeld

Here's the situation:
I have about 1000 cells. The first cell starts at a postive number (ie
5000). Each cell afterwards has a value smaller than the previous cell
(ie 5000, 4998, 4990, 4809....). Eventually the number will reach zero
or a negative number.

Once a cell reaches a zero or a negative number, I want all the
remaining cells after it to display nothing (or null or something like
that).

Here is my formula to do that:
=IF(I23<=0," ",(I23+((I23*($H$14/12))-J23)))

The above is basically saying:
IF(previous cell<=0 THEN display " " ELSE make a calculation to display
in the current cell)

This works for the first time, but anytime after that, it returns an
error (####).
So the excel sheet would look like this:
5000 4998 4990 4809 -23 #### ####

I know the problem lies in the fact that the IF statement says (IF
previous cell <=0). It won't work because the previous cell is #### (an
error).

How can I make it so that the #### is not displayed.

Here are my solutions:
1. Individually conditional format EVERY SINGLE cell. (I prefer not to
since there are 1000+ cells)
2. In each formula, create nested functions to accomplish what I'm
trying to do. (this is the ideal solution but I can't seem to figure
out the syntax. I've been trying OR functions with the IF but the
program doesnt seem to like it).

Maybe I could include another statement that says:
IF (previous cell <=0 OR isnull) THEN....

But how do I add the "OR isnull"? I don't know the correct syntax for
that.

Any help or advice please???
Click to expand...

Actually, your formula is returning a #VALUE! error, but your cell is too
narrow to show it.

It returns the value error because of the contents of the first cell that is
less than 0 which is either a null string or a <space>.

There are several possible solutions:

1. Use the formula: =IF(OR(I23<=0,I23=" ")," ",(I23+((I23*($H$14/12))-J23)))
(Your original post had a <space> (" ") but you could change it to a
null string ("").

2. It's pretty simple to apply conditional formatting to a contiguous range of
cells. Merely select them all and enter the Cell Value is less than or equal
to 0. Then format the font as white. All the cells should be appropriately
formatted. Or you can format one cell and use the format painter.

3. Use a custom number format. Something like #;; will not display values
that are zero or less.

In the case of solutions 2 or 3, you can simplify your formula to just the
equation in the 'condition if false' segment of the IF function.


--ron
 
Top