Search code examples
excelnested-if

Nested IF Question for Forecasting in Excel

I am trying to create a forecast based on a combination of trailing sales or as a % of last years sales. For the month of April 2020 I would like to apply trailing sales as my forecast, and for the remainder of the year I want to use a % of 2019 sales to forecast this years sales. Not sure what is most helpful, but I have the excel formula listed below:

IF(E37<>"",#N/A,IF(MATCH(MONTH(DATE($D$6,1,B13*7-2)-WEEKDAY(DATE(B13,1,3))),$C$2,0)>0,C$3,IF(MATCH(MONTH(DATE($D$6,1,B13*7-2)-WEEKDAY(DATE(B37,1,3))),$C$2,0)<>0,C13*A13,"")))

enter image description here

The first and second IF statements work fine, its the one that's supposed to calculate last years sales that the issue. I can share the sheet as well, not sure how best to do that.

Any help is, as always, greatly appreciated.

Solution

  • I think you have references that are off, Put this in E8 and copy down:

    =IF($D8<>"",NA(),IF(MONTH(DATE($D$6,1,$B8*7-2)-WEEKDAY(DATE($D$6,1,3)))=$C$2,$C$3,$C8*$A8)