When one does top-heavy testing, for example if auditing a retirement plan with a year ending 12/31/06, one would examine the asset balances of all participants as of the the last day of the previous year, in this case 12/31/05. Then one would total the account balances of the key employees (for the most part, owners of the company) and then express that as a percentage of all participants-but one excludes all participants who did not work for the company between 1/1/05 and 12/31/05.

Now, I had hit upon the idea of excluding all employees terminated before 1/1/05 by setting up a column reading Date of Termination and then using the YEAR function. So, someone terminating on 5/12/04 would return 2004. In another column, I would set up a formula to only return account balances if the year of termination equals or exceeds 2006. (One does an audit of a retirement plan usually a few years after the actual filing of the 5500 return.) However, it has quickly occurred to me that a person who never terminated would also get excluded, since his or her year value would equal 0.

An even more complicated challenge; some retirement plans have fiscal years. So, in that case, the year under audit could end on 9/30/2006. Therefore, the previous year would end on 9/30/2005. Therefore, one would have include those terminated participants who worked from 10/1/2005 to 9/30/2005. See the problem? The year value for 1/1/2005 and 10/2/2005 remains the same, even though those actually represent different plan years (1/1/2005 would fall in the plan year ending 9/30/2005 while 10/2/2005 would fall in the plan year ending 9/30/2006).

Can one set up an "If" function with two conditions?