shorten so many if statements

[wannabe12]

Hi there, in cell E6 I have this formula -

=IF(F6=0,(IF(G6=0,(IF(H6=0,(<wbr>IF(I6=0,(IF(J6=0,(IF(K6=0,(IF(L6=0,(IF(M6=0,(IF(N6=0,(IF(O6=0,(IF(P6=0,(IF(Q6=0,(IF(R6=0,(IF(S6=0,(IF(T6=0,(IF(U6=0,(IF(V6=0,(IF(W6=0,"1","0")),"0")),"0")),"0")),"0")),"0")),"0")),"0")),"0")),"0")),"0")),"0")),"0")),"0")),"0")),"0")),"0")),"0")
<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>

Basically evaluating if a cell is zero and if it is move on to the next cell, and then ***** again.
If all cells equal 0 display 1 otherwise display 0.

There are formulas in these cells so ISBLANK doesnt work.

Im sure theres a much simpler formula that can be used instead.

Any questions just email back.

Merry Christmas (yeah I know its early.lol)

Amish

 

[Smitty]

Welcome to the Board!

Maybe something like this:

=IF(COUNTIF(F6:W6,">0")=0,0,1)

HTH,

 

[hippiehacker]

Hi there, in cell E6 I have this formula -

=IF(F6=0,(IF(G6=0,(IF(H6=0,(<wbr>IF(I6=0,(IF(J6=0,(IF(K6=0,(IF(L6=0,(IF(M6=0,(IF(N6=0,(IF(O6=0,(IF(P6=0,(IF(Q6=0,(IF(R6=0,(IF(S6=0,(IF(T6=0,(IF(U6=0,(IF(V6=0,(IF(W6=0,"1","0")),"0")),"0")),"0")),"0")),"0")),"0")),"0")),"0")),"0")),"0")),"0")),"0")),"0")),"0")),"0")),"0")),"0")
<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>

Basically evaluating if a cell is zero and if it is move on to the next cell, and then ***** again.
If all cells equal 0 display 1 otherwise display 0.

There are formulas in these cells so ISBLANK doesnt work.

Im sure theres a much simpler formula that can be used instead.

Any questions just email back.

Merry Christmas (yeah I know its early.lol)

Amish

I don't know if there is any better one but I would use something like this

=IF(SUM(F6:W6)=0;1;0)

 

[dave3009]

One more effort

=IF(MAX(F6:W6)>0,1,0)

 

[hippiehacker]

another one could be

=IF(AVERAGE(F6:W6)=0;1;0)

 

[Rick Rothstein]

Let's get rid of the IF function call completely...

=1*(SUM(F6:W6)=0)

 

[Momentman]

Maybe use:

=IF(COUNTA(F6:V6)=COUNTIF(F6:V6,0),1,0)

 

[wannabe12]

Thanks eveyone I think Ive got plenty to try out.

Merry Christmas!!

 

[joms101]

Hi,

Can you help me shorten so many IF statements such as this:

=IF(O10>$E$3,O10,IF(Q10>$E$3,Q10,IF(S10>$E$3,S10,IF(U10>$E$3,U10,IF(W10>$E$3,W10,IF(Y10>$E$3,Y10,IF(AA10>$E$3,AA10,IF(AC10>$E$3,AC10,IF(AE10>$E$3,AE10,IF(AG10>$E$3,AG10,IF(AI10>$E$3,AI10,IF(AK10>$E$3,AK10,IF(AM10>$E$3,AM10,IF(AO10>$E$3,AO10,IF(AQ10>$E$3,AQ10,IF(AS10>$E$3,AS10,IF(AU10>$E$3,AU10)))))))))))))))))

Thanks,

Joms

 

[Rick Rothstein]

Can you help me shorten so many IF statements such as this:

=IF(O10>$E$3,O10,IF(Q10>$E$3,Q10,IF(S10>$E$3,S10,IF(U10>$E$3,U10,IF(W10>$E$3,W10,IF(Y10>$E$3,Y10,IF(AA10>$E$3,AA10,IF(AC10>$E$3,AC10,IF(AE10>$E$3,AE10,IF(AG10>$E$3,AG10,IF(AI10>$E$3,AI10,IF(AK10>$E$3,AK10,IF(AM10>$E$3,AM10,IF(AO10>$E$3,AO10,IF(AQ10>$E$3,AQ10,IF(AS10>$E$3,AS10,IF(AU10>$E$3,AU10)))))))))))))))))

This array-entered** formula should do what the formula you posted does (except that it returns 0 instead of FALSE when the value in E3 is greater than all the values in the cells being checked)...

=INDEX(A10:AU10,,MIN(IF((O10:AU10>$E$3)*(MOD(COLUMN(O10:AU10),2)=1),COLUMN(O10:AU10))))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself