# working around blank value in IF statements

S

#### spence

I have the below formula to calulate benefit increases depending on various
plan dates. It's working perfectly for what I need except that in some
instances there will be no value in cell B3 (a date). I need to figure out
how to rework things so that if B3 is blank the calculations in my IF
statements will still occur and I'm in over my head. Any assistance is
appreciated.

Thanks,
spence

=IF(AND(\$B\$3>\$N\$1,\$B\$1>\$N\$1,\$B\$4>\$N\$2),\$B\$5*((\$M\$2/12)*((YEAR(\$B\$2)-YEAR(\$L\$2))*12+MONTH(\$B\$2)-MONTH(\$L\$2)+1)+1),IF(AND(\$B\$3<\$L\$1,\$B\$1<\$L\$1,\$B\$4<\$L\$2),\$B\$5*((\$M\$1/12)*((YEAR(\$B\$2)-YEAR(\$L\$1))*12+MONTH(\$B\$2)-MONTH(\$L\$1)+1)+1),IF(AND(\$B\$3<\$L\$1,\$B\$1<\$L\$1,\$B\$4>\$N\$2),\$B\$5*((\$M\$1/12)*((YEAR(\$B\$2)-YEAR(\$L\$1))*12+MONTH(\$B\$2)-MONTH(\$L\$1)+1)+1)*((\$M\$2/12)*((YEAR(\$B\$2)-YEAR(\$L\$2))*12+MONTH(\$B\$2)-MONTH(\$L\$2)+1)+1),\$B\$5)))

P

#### Pete_UK

You have:

IF(AND(...

three times, and within each of your ANDs you have some comparison
involving B3.

So, if B3 is blank do you want to return B5? If so, try this addition

=IF(\$B\$3="",\$B\$5, your_existing_formula )

Hope this helps.

Pete

R

#### RagDyer

If I understand you, try this:

=IF(AND(or(\$B\$3>\$N\$1,\$B\$3=""),\$B\$1>\$N\$1,\$B\$4>\$N\$2),\$B\$5*((\$M\$2/12)*((YEAR(\$B\$2)-YEAR(\$L\$2))*12+MONTH(\$B\$2)-MONTH(\$L\$2)+1)+1),IF(AND(or(\$B\$3<\$L\$1,\$B\$3=""),\$B\$1<\$L\$1,\$B\$4<\$L\$2),\$B\$5*((\$M\$1/12)*((YEAR(\$B\$2)-YEAR(\$L\$1))*12+MONTH(\$B\$2)-MONTH(\$L\$1)+1)+1),IF(AND(or(\$B\$3<\$L\$1,\$B\$3=""),\$B\$1<\$L\$1,\$B\$4>\$N\$2),\$B\$5*((\$M\$1/12)*((YEAR(\$B\$2)-YEAR(\$L\$1))*12+MONTH(\$B\$2)-MONTH(\$L\$1)+1)+1)*((\$M\$2/12)*((YEAR(\$B\$2)-YEAR(\$L\$2))*12+MONTH(\$B\$2)-MONTH(\$L\$2)+1)+1),\$B\$5)))