I think an =IF(AND will work but I'm not sure

[mikeniedert]

I have a worksheet that will be used to forecast annual costs for contracts of various term lengths. In my cell C9 I enter in the number of months in the contract term (i.e., 24). This number is variable depending on the term length of the contract. I'd like a formula I can use put in C39, D39, E39, etc. that would display "Year 1", "Year 2", "Year 3", and etc. and then at the end display "Total"

So for example if cell C9 has 28 (for a contract that is 2 years and 4 months)...cell C39 would show "Year 1", Cell D39, would show "Year 2" and cell E39, would show "Year 3" and cell F39 would show "Total". If cell C9 is 12, then C39 would show "Year 1" and cell D39 would show "Total"

Thanks in advance for the help.

 

[mart37]

=IF(COLUMN()-2<=ROUNDUP($C$9/12,0),"Year "&COLUMN()-2,IF(COLUMN()-2=ROUNDUP($C$9/12,0)+1,"Total",""))