Nested IF formula

Dear All,

I used following formula both as normal & array -

=IF(AND($L$2:$L$98=0,$M$2:$M$98=0),STDEV(B2:B98),"-")

The normal formula is returning #VALUE while array one is returning " - ".

Please help where my problem lies.

Regards,

Dastgir

The Excelphile,

Cell range $L$2:$L$98 & $M$2:$M$98 is not intended to be sumed here. The condition is that if any cell value is zero in this range, then STDEV of defined range.

Dastgir

Originally Posted by TheExcelphile

Hi Dastgir,

I assumed that your formula meant if all cell values are zero in L2:L98 and M2:M98.

According to your clarification, your formula should be:

=IF(OR(MIN($L$2:$L$98)=0,MIN($M$2:$M$98)=0),STDEV(B2:B98),"-")

Don't forget to click the little star to the left of this post if you feel I helped!

Dear Excelphile,

Why AND or any other logic is not applicable for a cell range, while it works fine with single cell condition.

Dastgir

Originally Posted by :) Sixthsense :)

I am not aware the usage of STDEV function, but in general term the formula should be constructed like the below

Array Formula - Requires Ctrl+Shift+Enter

Formula:

=STDEV(IF(($L$2:$L$98=0)*($M$2:$M$98=0),(B2:B98)))

s,

Dear Sixthsense,

As we use * for AND use, is there any symbol which can be used for other logics such as OR , NOT etc.

Regard

Dastgir