IF Statements etc.

RIVIERA

New Member
Joined
Oct 24, 2002
Messages
11
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
{=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

New Member
Joined
Oct 24, 2002
Messages
11
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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234

ADVERTISEMENT

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
 

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654

ADVERTISEMENT

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

Try mine!
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
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
 

Forum statistics

Threads
1,144,058
Messages
5,722,269
Members
422,419
Latest member
Havok390

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top