we have forecast data in weekly format.
In each row we will have 26 columns ,each column represent weekly demand qty for that material.
Ex:
Material w1 w2 w3 .....w26
Now we need to create a calculated column which will give an average of 26 weeks.if its a simple avg then we can write like below.
Calculated col=(w1+w2+.....+w26)/26
But the denominator in the above formula should change for each row according to the number weeks which has demand.
Ex:for a given material out of 26 weeks if only 23weeks has demand and other 3 weeks as zero demand then we have to divide the sum by 23 but not 26.
Any help /input on this is really appreciated.