stormania Member Jan 25, 2023 #1 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 Attachments FAcy.xlsx FAcy.xlsx 10.1 KB · Views: 3
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
B bosco_yip Excel Ninja Jan 25, 2023 #2 Try, In E2, formula copied down: =IF((A2>0)*(B2>0),IF(D2>1,0,1-D2),0) Last edited: Jan 25, 2023
B bosco_yip Excel Ninja Jan 25, 2023 #5 stormania said: Could you explain D2>1 ? why not use D2>100? Thank you Click to expand... Or You can shorten it using 1 IF function only: =IF((A2>0)*(B2>0)*(D2<1),1-D2,0)
stormania said: Could you explain D2>1 ? why not use D2>100? Thank you Click to expand... Or You can shorten it using 1 IF function only: =IF((A2>0)*(B2>0)*(D2<1),1-D2,0)
