Robust formula

[Peggy2015]

Hello,

I have a formula:

=IF(OR(E6<>0,F6<>0,G6<>,H6<>0,I6<>0,J6>0,K6<>0,L6<>0,M6<>0,N6<>0,O6<>0,P6<>0,Q6<>0),"!"," ")

There must be an easier way to write this formula that does better job?

Thanks,
Peggy

 

[wideboydixon]

"G6<>" and "J6>0" I'm going to assume are typos. How about:

=IF(SUMPRODUCT(--($E6:$Q6<>0))>0,"!"," ")

WBD

 

[jtakw]

.

 

[jtakw]

Hi,

Also assuming G6 and J6 are typos in your formula:

=IF(SUM(E6:Q6),"!","")

You had a SPACE " " as Value if False, I've used a Blank "" , change it if you want.

 

[mrhstn]

Enter this with ctrl+shift+enter

=IF(OR(E6:Q6<>0), "!", " ")

 

[wideboydixon]

Hi,

Also assuming G6 and J6 are typos in your formula:

=IF(SUM(E6:Q6),"!","")

You had a SPACE " " as Value if False, I've used a Blank "" , change it if you want.

E6 = 1, F6 = -1, G6:Q6 = 0 and it doesn't produce the correct result.

WBD

 

[jtakw]

E6 = 1, F6 = -1, G6:Q6 = 0 and it doesn't produce the correct result.

WBD

You're right, forgot about possible Negatives...

May be this then:

=IF(COUNTIFS(E6:Q6,"<>0",E6:Q6,"<>"&""),"!","")

 

[jtakw]

Or, if the range could Never have blank cells:

=IF(COUNTIF(E6:Q6,"<>0"),"!","")