#1
|
|||
|
|||
Conditional formatting
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. |
#2
|
||||
|
||||
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 |
#3
|
|||
|
|||
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. |
#4
|
||||
|
||||
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 |
#5
|
|||
|
|||
Here is a sample G3 shows divide by zero error.
|
#6
|
||||
|
||||
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 |
#7
|
|||
|
|||
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. |
#8
|
|||
|
|||
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. |
#9
|
|||
|
|||
Sorry uploaded sheet.
|
#10
|
|||
|
|||
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))) |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
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 |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Conditional formatting | dayperson | Excel | 1 | 02-23-2016 09:07 AM |
Conditional formatting | marif300 | Project | 9 | 09-23-2014 07:23 AM |
Conditional formatting that ignores other formatting rules | info_guy2 | Excel | 1 | 07-03-2014 10:07 AM |
Conditional Formatting. | Laurie B. | Excel | 6 | 04-09-2012 05:01 PM |
Conditional formatting | zanat0s | Excel | 4 | 01-20-2012 03:30 AM |