IF Statements etc.

[RIVIERA]

I have the following statementin D4:-

=IF(OR(E4<C4,H4<C4,K4<C4,N4<C4,Q4<C4,T4<C4,W4<C4,Z4<C4,AC4<C4,AF4<C4,AI4<C4,AL4<C4,AO4<C4,AR4<C4,AU4<C4,AX4<C4,BA4<C4,BD4<C4,BG4<C4,BJ4<C4),"ALARM","OK")

However only E4 currently has any data in, the rest have #value. How do I get the statement to ignore the #value cells until they're populated?

Thanks

 

[PaddyD]

first things first!

change the horrible OR to something of the form:

=if(min(myrange)<c4,"ALARM","OK")

where my range ios set up by selecting all the cells of interest (holding dowm the control key) & entering an appropriate name in the name box next to the formula bar.
This message was edited by PaddyD on 2002-10-29 18:59

 

[Mark W.]

{=IF(OR(IF((COLUMN(E4:BJ4)=TREND({5;8},{1;2},ROW(1:20)))*ISNUMBER(E4:BJ4),E4:BJ4<C4)),"ALARM","OK")}


Note: This is an array formula which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula. For more on array formulas see the Excel Help topic for "About array formulas and how to enter them".
This message was edited by Mark W. on 2002-10-29 19:15

 

[RIVIERA]

Tried that, can't seem to get it to work. Still returns #value in D4.

=IF(MIN(E4,H4)<C4,"ALARM","OK")

Might it have some thing to with E4:-

=VALUE(LEFT(DATA_IMPUT!B3,4))

which is displayed 1.38?

Thanks again

 

[PaddyD]

the formula I posted wasn't intended to deal with the value errors, just to point out that there was no need to have the massive OR to do the comparison. anyway - see what Mark W.'s solution does for you...

paddy

 

[Aladin Akyurek]

How come you have #VALUE error(s) in the cells of interest?

 

[Mark W.]

On 2002-10-29 19:17, RIVIERA wrote:
Tried that, can't seem to get it to work.

Try mine!

 

[PaddyD]

On 2002-10-29 19:30, Mark W. wrote:

On 2002-10-29 19:17, RIVIERA wrote:
Tried that, can't seem to get it to work.

Try mine!

Just to reiterate, and 'cos it's a rare occasion when I can say that this was deliberate - my suggestion was not meant to work

 

[Mark W.]

PaddyD, I realize that. I believe that the RIVIERA didn't see my reply, and I was attempting to draw his attention.
This message was edited by Mark W. on 2002-10-29 19:48