I have attached workbook with data. The workbook is utilizing Excel's table format formulas. I have tried to write a single formula to handle three scenarios:
1. Remain. Life (D) > 0 and Amount to Fund Replac. (K) > 0 FALSE --- answer should be: $1,600
2. Remain. Life (D) = 0 and Amount to Fund Replac. (K) > 0 $500 --- answer is correct
3. Remain. Life (D) = 0 and Amount to Fund Replac. (K) = 0 FALSE --- answer should be: blank or zero
The formula is: =IF([@[Remain. Life]]=0,IF(AND([@[Amount to Fund Replac. ]]>0),[@[Amount to Fund Replac. ]]/5,(IF([@[Remain. Life]]>0,[@[Amount to Fund Replac. ]]/[@[Remain. Life]]))))
Any help is appreciated.
Roger
Bookmarks