Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
working around blank value in IF statements
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))) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
working around blank value in IF statements
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 to what you already have: =IF($B$3="",$B$5, your_existing_formula ) Hope this helps. Pete On Aug 26, 10:32*pm, spence wrote: 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))) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
working around blank value in IF statements
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))) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "spence" wrote in message ... 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))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF STATEMENTS NOT WORKING | Excel Worksheet Functions | |||
Solution for charting IF statements that return blank values | Charts and Charting in Excel | |||
working with IF statements | Excel Worksheet Functions | |||
Working with If statements and time | Excel Worksheet Functions | |||
4 different if statements, not working | Excel Worksheet Functions |