=((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

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.

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]

Hah...of course.

Thanks,
I'll study that.
I think I may find uses for it elsewhere.

Gary

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

__________________
Using O365 v2503 - 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

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

Perhaps this is a bit simpler

__________________
Using O365 v2503 - 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