#1
|
|||
|
|||
How does this Conditional Format work?
=((TYPE(H4)<>2)*(H4<>""))*OR(H4<$E4,H4>$G4)
I had help getting this conditional format, which highlights a cell if its' contents fall outside a min. max range on the spreadsheet. I really don't understand how it works though. Can someone explain it to me please? Thanks, Gary |
#2
|
|||
|
|||
These are two 2x2 matrices multiplied in series.
The first is the result of a multiplication of two tests, which is basically an AND test if I'm thinking straight. If H4 is not text (i.e. not Type=2) AND if H4 is not blank, then TRUE, otherwise FALSE. The second part, I would prefer ISBLANK(H4). Thus I would write it... =AND(TYPE(H4)<>2,ISBLANK(H4)) The second is an OR of H4<E4 and H4>G4. Obviously, an equivalence in either, H4 equals E4 or G4, would result in a FALSE. Thus, if either E4>H4 OR H4>G4, Then TRUE, otherwise FALSE. The end result is a number, either 1 or 0, TRUE or FALSE, etc. That result I suppose is conditionally formatted. If you could attach a sample sheet, I could see how it works. It seems kludgy. |
#3
|
||||
|
||||
It's basically equivalent to:
=AND(TYPE(H4)<>2,H4<>"",OR(H4<$E4,H4>$G4)) or: =(TYPE(H4)<>2)*(H4<>"")*((H4<$E4)+(H4>$G4)) or: =AND(TYPE(H4)<>2,NOT(ISBLANK(H4)),OR(H4<$E4,H4>$G4 )) gebobs: ISBLANK(H4) is not equivalent to H4<>"", but NOT(ISBLANK(H4)) is.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#4
|
|||
|
|||
Hah...of course.
|
#5
|
|||
|
|||
Thanks,
I'll study that. I think I may find uses for it elsewhere. Gary |
#6
|
||||
|
||||
Also keep in mind that TYPE()2 will yield TRUE for numbers but also for error values ,logical values or arrays. So perhaps =TYPE()=1 would be safer
__________________
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
|
|||
|
|||
Safer? In this case, it is referencing numbers only.
But can you explain further, and give me a couple of examples of such error values, logical values and arrays? I don't think I will get in that deep, but what might be an expected outcome? I'll give the =TYPE()=1 a try. =((TYPE(H4)<>2)*(H4<>""))*OR(H4<$E4,H4>$G4) How would apply your suggestion to this formula? Thanks, Gary |
#8
|
||||
|
||||
Perhaps this is a bit simpler
Code:
=isnumber(h4)*OR(H4<$E4,H4>$G4)
__________________
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 |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Conditional Format cell appearance Excel 2007 | Gary Drumm | Excel | 3 | 02-22-2014 01:43 AM |
Applying a conditional format to multiple rows | secoo140 | Excel | 1 | 10-12-2013 07:19 PM |
Table with Conditional Format to Word | billyV | Excel | 9 | 06-07-2013 08:26 AM |
Copy format created by a conditional formatting | spk | Excel | 2 | 04-10-2013 04:41 AM |
Conditional format for form drop-down list? | pmregan | Word | 1 | 09-19-2012 07:09 PM |