Formula multiple condition

[stormania]

Dear Excel master

Kindly help to find formula to calculate multiple if condition that value should be in in range 0%-100%, not include blank or #DIV, or value above 100% as per attach file.



Thank you in advance

 

[bosco_yip]

Try,

In E2, formula copied down:

=IF((A2>0)*(B2>0),IF(D2>1,0,1-D2),0)

 

[stormania]

Could you explain D2>1 ? why not use D2>100? Thank you

 

[vletm]

stormania
1 = 100%

 

[bosco_yip]

Could you explain D2>1 ? why not use D2>100? Thank you

Or

You can shorten it using 1 IF function only:

=IF((A2>0)*(B2>0)*(D2<1),1-D2,0)

 

[stormania]

Ok thank you. SOLVED