• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

IF(S) Condition

K

kimknguyen

Member
Dear Excel Ninjas and Experts:
Please help with this formula:
OBJECTIVE: for GoalNew column is to estimate sale for the next few years using previous year's sale and year-over-year estimated % increase.:

METHOD: use AVE sale figure from for each day of the week (Mon, Tues…) in the same month from prior year, time % increase (using table YrOYr)

NOTE 1: For 2016, GoalNew is Goal (just an estimate to star for first year). For 2017 and beyond, use sale figure from previous year to calc target goal.
NOTE 2: was calc the condition for when have sale BUT NEED HELP if the IF statesmen for when do not have sale yet and use previous year's GoalNew (vs. Sale)
Thank you!
 

Attachments

  • Book2.xlsm
    93.5 KB · Views: 8
L3: =IFERROR(INDEX([Sale],MATCH(EDATE([@Date],-12),[Date],0))*(1+VLOOKUP([@Year],YrOYr,2)),0)
 
Hi Hui,
Thank you for the quick response. The calculation did not give the desire result. Perhaps I was not clear in my explanation…The problem is:
IF year is >=2017, and IF there are sale number then (1) to look up all the day of the weeks for a particular month (usually 4-5 Monday in Jan for example) from the year before.
(2) calc the ave sale for those 4-5 weekdays. (3) time the guestimate percent increase (from table YrOYr).
(4) if there is NO sale number then do the same as 1, 2, 3 but use GoalNew (from the ave for same day of the week, same month of the month, but for previous year).

IF year is <2017 the use Goal (column K).

Would you please take a another look...?
Thank you.
 
Do you want the average for Week 1 of the previous year or All Mondays in january from the previous year ?

A worked example would clarify your requirements
 
Can you please try:
L3: =SUMPRODUCT(([Day]=B3)*(MONTH([Date])=MONTH(A3))*(YEAR([Date])=YEAR(A3)-1)*([Sale]))*(1+VLOOKUP([@Year],YrOYr,2))
 
Ave of all Monday in Jan, Feb, Mar...
Ave of all Tues in Jan, Feb

The idea is that since it is seasonal and day of the week sale, we'd to follow customers pattern for the planning and forecasting.

Thank you Hui,
Kim
 
Hi:

I have applied the logic you have asked in column 4 , I am not sure how you got the values that I have colored in red, could you please explain and show the calculation manually.

Thanks
 

Attachments

  • Book3.xlsm
    105.2 KB · Views: 1
hi Nebu,

There were errors in my calc. in previous post. My apologies.
Please see updated file. Column T is manual calc from pivot table. For 2017 just use the ave of each day of the week for each month in Jan and time 1.25. For 2018 use value in of 2017 and time 1.25.
I was able get same result (column n) for when we have Sale but still figuring out for when not have Sale (use value in column n) as in manual calculation for 2018.

Another issue I find is that the SUMPRODUCT if use in column n is very unstable so I am also trying to use other function such as SUMIFs or other.

Ignore column L (I was playing with different functions).

In short, the value in column t is what I need but have issue in finding working formula.
Thank you for your help!
 

Attachments

  • Book3.xlsm
    181 KB · Views: 3
Hi:

Find the attached. I had to use helper column to get rid of circular reference. My formula uses average ifs not sum product.

Thanks
 

Attachments

  • Book3 (1).xlsm
    183.6 KB · Views: 5
kimknguyen said:
WOW! Thank you Nebu!
Click to expand...
Hi Nebu,

...need to pick your brain again....with the same problem but instead of ave every Mon in Jan (for example), how can we look up each day of the week for the same fiscal week of prior year and time 1.25. So no average calculation is needed.
Each year we have 52 weeks and each week has Mon, Tue, Wed.....
So if we look up Sale of each day of the week for the same fiscal week in prior year. If no Sale yet then use the GoalNew.

Thank you so much!
Kim
 
Hi:

I could not understand what you meant, could you upload something with the calculation done manually.

Thanks
 
Hi Nebu, we have two errors: Zero and #DIV/0. Please see Book5. It has just a few samples of the error from a big table with data extend to 2020.

Please review.
Thanks,
kim
 

Attachments

  • Book5.xlsm
    295.4 KB · Views: 4
Hi:
I am travelling and will be accessing this forum sporadically. I will try to get to your file whenever I can.

Thanks
 
You must log in or register to reply here.
Back
Top