• 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.

formula that takes average of next 8 weeks. weeks are moving

Rick1001

New Member
Hi, I need help in finding a formula that takes average of next week using current week as week 1 from list of Skus
For example
E6 should find next 8 weeks from column C & then find average of 8 weeks from column D & then return value in E6 of Sku in B6
Spreadsheet attached

Thanks in Advance
 

Attachments

  • Excel Chand.xlsx
    13.7 KB · Views: 5
Perhaps this one might just do
[E6]=AVERAGE(OFFSET(D6,0,0,8))
drag down.

EDIT: non volatile
=AVERAGE(D6:INDEX(D6:$D$524288,8))
 

Attachments

  • Copy of Excel Chand.xlsx
    18.4 KB · Views: 2
Last edited:
Rick1001
Average is ~38,89 ( .. those weeks)
... then You ask to return value in E6 of Sku in B6
Aren't all Sku-values same?
Could You show some needed results?
 
Rick1001
Okay - those are Your 8 weeks ...
Next: find average of 8 weeks from column D --- can see below ~39 ( ~38,89 ) as You have asked - or what?
Screenshot 2019-08-22 at 09.31.17.png
and if ... Skus .. would be different ... then those should be different
 
Then you should answer Vletm's questions too and make the data representable of real life situation and provide an example or two. So we know exactly what you are after.
 
Rick1001
Okay - those are Your 8 weeks ...
Next: find average of 8 weeks from column D --- can see below ~39 ( ~38,89 ) as You have asked - or what?
View attachment 62287
and if ... Skus .. would be different ... then those should be different
Thats correct - average of 39
There will be lot of different Skus number
See attached an example - it will make more sense
 

Attachments

  • Excel Chand.xlsx
    14.6 KB · Views: 10
Rick1001
Where comes those four yellow cell values?
Your B-column has ... two kind of values...
Hi Rick
Yellow cell values that are not in the column B are just examples 8****33 & 8****34
Column B in real life could have 100 of different skus with two years of weekly data
 
Rick1001
... examples
... yes, but even samples should be something which could use.

If Your 'Desired Results' has four different Skus
... then that's a challenge for others than You to find those!
( needed correct results are much harder to find than other results )
Screenshot 2019-08-22 at 10.29.00.png
Could You one more time sit and ... create clear samples of You needs?

Hint - idea
... I would use for 'weeks' format like year.week eg 2019.33 instead of 33.2019 ( if someone sort those You columns be 'week')
 
Rick1001
... examples
... yes, but even samples should be something which could use.

If Your 'Desired Results' has four different Skus
... then that's a challenge for others than You to find those!
( needed correct results are much harder to find than other results )
View attachment 62296
Could You one more time sit and ... create clear samples of You needs?

Hint - idea
... I would use for 'weeks' format like year.week eg 2019.33 instead of 33.2019 ( if someone sort those You columns be 'week')
hi
if a formula cant find Skus than it should return blank.
in real life scenaro,There would be around 1000 Skus in the table & around 400 in the Desired result
 
Rick1001
I haven't use any formulas not yet,
because Your samples with desired results no match!
Your samples are here 'the real life scenaro'
... if all 'skus' are same in Your given data, then no need any formula here!
... but in the real life ... that won't work well.
 
Helper column in E
[E6] =IF(C6=$B$1,1,IF(AND(E5<8,RIGHT($B$1,4)=RIGHT(C6,4),(LEFT($B$1,2)+E5)=(LEFT(C6,2)*1)),E5+1;0))

For averages, if you have the function onboard
=IFERROR(AVERAGEIFS($D$5:$D$156,B$5:$B$156,L7,$E$5:$E$156,"<>"&0),"")
 

Attachments

  • Copy of Excel Chand_avrgIfs.xlsx
    16 KB · Views: 3
Thats correct - average of 39
There will be lot of different Skus number
See attached an example - it will make more sense
Based on the OP's file attached in Post #.07, here's the formula solution without helper.

In I18, formula copied down :

=IFERROR(AVERAGE(OFFSET($D$5,MATCH(1,INDEX(($B$6:$B$1000=H18)*($C$6:$C$1000=$B$1),0),0),0,8)),"")

Regards
Bosco
 
Back
Top