Match or Offset formula needed to forecast annual data

[Hunter Childress]

Hi,

Attached is my template. I need the formula entered into the yellow cells.

Formula Needed: In I31, I need a formula I can drag and fill. If today's date is less than the month in row 30, then it should pull the blue Hard coded data above. If today's date is more than the month in row 30, then if the monthly date is in the year of the annual data in c4, it should take the data and divide by 12.

Historical Actual (blue)- if the date is less than today, it should pull the historical actual in blue.

Forecast - If the is more than today, it should determine if the month is the annual data then pull the annual datas cell and divide by total amount.

Thanks,

Hunter

 

[eibi]

Hunter,

I'm not sure how well I have understood your request --

Assuming that you enter today's date in A1, paste this formula in I31 and see if it gives you the desired output:

=IF(I$30<$A$1,I4,INDEX($C$4:$F$4,,MATCH(I$30,$C$3:$F$3,1))/12)

See attached...

Is this heading the right direction?