Office 2007 Excel

I don't want to show a cell if it is a value divide by zero or negative.

I still get divide by zero error. This seems to over rule the condition.

What do you mean by " show a cell"?

Conditionally format as background if DIV0 error or negative?

Then this should work as conditional format
=OR(TYPE(A1)=16,A1<0)

Format as reuired

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

What I wanted was to change the font colour to white so you never saw any text. Preferably dont't put anything in the cell.

I made a condition if less than one font colour white. This still is in black stating #Div /0! in Blue bold text.

Please post a small sample sheet showing what you have and desired results Thx

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Here is a sample G3 shows divide by zero error.

Attached Files

Sample.xlsx (21.4 KB, 9 views)

As the cell value equates to an error, your condition that the cell value be smaller than 1 is not valid

Why?

When comparing, XL first compares the TYPE value of the data ( see that function for more information)


This function returns 16 for an error value and 1 for a number.
As 16 is not smaller than 1, your condition returns FALSE, the CF is thus not applied.

Instead go to Cond Formatting - New rule - select "use a formula" and enter =or(type($g3)=16,$G3<0)
and format as necessary after selecting a column range starting in G3

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

That is not exactly correct. Look at it today. If you change the value in cell D3 so that the divide by zero moves to F3 then...

G3 will not be a divide by zero as it is a divide by -1 now.

-1 is less than one isn't it.

I have posted a sheet that shows the problem fully

Here if Error display in orange
If less than 1 display in orange

=or(type(G$3)<>16,G$3>1)

Row 3 should fail the conditions as divisor is greater than 1. Should be blue.

Sorry uploaded sheet.

Hi

How about in G3:

=IFERROR(SUM((A3+$K$2)/(B3-2)),"")
or
=IF(ISERROR(SUM((A3+$K$2)/(B3-2))),"",SUM((A3+$K$2)/(B3-2)))

Thanks Kevin these work when combined with conditional formatting less than 1.

Don't fully understand the if error function and I don't understand the difference between the 2 examples you show. The second has additional stuff.

I had to edit the formula with the '$'

=IF(ISERROR(SUM(($A3+$K$2)/($B3-2))),"",SUM(($A3+$K$2)/($B3-2)))

Need to get a book on this especially the use of macros. Have used them in Word

Hi otuatail

Some people put their version of excel in their profile and on some occasions it is incorrect. The first example should do you the second is for versions older than Excel 2007.

Kevin