Nested IF Question for Forecasting in Excel

0
votes

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.

2
excelnested-if
What values are in E38 and B38? we cannot see those values. - Scott Craner

2 Answers

0
votes

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)
0
votes

For anyone who's curious, here is the formula that I used to complete my follow up question. Instead of limiting trailing sales to monthly blocks, I decided to change it to a weekly level. The range for weeks can be increased as needed. 

IF(D8<>"",NA(),IF(ISNUMBER(MATCH($B8,$C$2:$D$2,0)),C$3,IF(NOT(ISNUMBER(MATCH($B8,$C$2:$D$2,0))),$A8*C8)))