Set formula to calculate 0 when 'D6' is empty

[JadonR]

I have the following formula:

=IF(D5="Fixed Price",0.5,IF(AND((D6*D9)>=0.01,(D6*D9)<=0.99),0,IF(AND((D6*D9)>=1,(D6*D9)<=9.99),0.25,IF(AND((D6*D9)>=10,(D6*D9)<=24.99),0.5,IF(AND((D6*D9)>=25,(D6*D9)<=49.99),0.75,IF(AND((D6*D9)>=50,(D6*D9)<=199.99),1,IF(AND((D6*D9)>=200,(D6*D9)<=499.99),2,IF((D6*D9)>=500,2,0))))))))*IF(G16="Yes",2,1)

I want it to show zero if cell 'D6' is also zero. I tried nesting more IF statements but keep getting errors. Any ideas?

 

[jasonb75]

Try

=IF(D5="Fixed Price",0.5*(D6>0),(MATCH(D6*D9,{0,1,10,25,50,200})-1)*0.25)*IF(G16="Yes",2,1)

With a few test figures, does it give the desired results?

 

[JadonR]

Impressive, that really shortened it up. It works perfect until cell 'D6' is at or above 200. Then it shows 1.25 instead of 2.00. Maybe an easy fix?

 

[jasonb75]

Oops, I missed that step-up point.

=IF(D5="Fixed Price",0.5*(D6>0),(MATCH(D6*D9,{0,1,10,25,50,200,200,200,200})-1)*0.25)*IF(G16="Yes",2,1)

Fixes that, or as an alternative

=IF(D5="Fixed Price",0.5*(D6>0),LOOKUP(D6*D9,{0,1,10,25,50,200},{0,0.25,0.5,0.75,1,2}))*IF(G16="Yes",2,1)

which I would imagine would be the version that the majority of users would suggest.

 

[JadonR]

Awesome. That fixed it. Thanks Jason!