Hello ,
1st i`ll try to explain what m trying to achieve, i`ve attached the demo doc with this thread please find the attachment
I have different categories in column A and have 3 different weeks devided in section.
U can see total sales section in row 3 and sales and change section in every week.
Sales have 3 section No. and % share of the total below that.
Now what i can do is i am able to calculate the change in sales of the current week and the past week, but in case there is no value in last week sales but have a values before that week
eg. Category B week 3 change has to be calculated , i have sales data for Week 1 and Week 3 so how do i calculate the change in data
I am dealing with more than 40 weeks data here ! Need a generalise formula or script that check if there is a sales value in the previous week or it runs till it finds a week that has a sales value in the same category. and calculate the change.
Normally i can calculate the change from the last week but there are cases when there is no value for last week in the same category then i`ve to check which week has the same category entry and have to calculate manually.
Say i have to calculate the value in M7 so it can be calculated by calculating sales change from week 3 compared to week 1
Bookmarks