Hello,
I have a problem where I have to calculate average of 12 months sales for every customer and then compare every month sale from average to give difference between average and actual sales value. But my data has missing data (essentially 0 sales) for some months for all customers. When trying to subtract average from 0 (after replacing sum with zn(sales)) it is not giving me any value.
I am attaching worksheet of what I have done along with the screenshot using superstore data. If someone can help me figure out where I am doing wrong is very much appreciated.
Average is calculated by simply taking 12 month revenue and by diving it by 12. Here I am not using the average or window average function.
Regards,
Saurabh Ashtamkar
Hi the issue is that there are no record nulls in the data (see https://jimdehner2.blogspot.com/2020/01/faq-series-not-all-nulls-are-created.html to understand different null types) and you want to use the actual missing cell in a series of calculations - you will need to force a real cell into the data set using a scaffold to accomplish what you want - I spoke recently in a series - the first session speaks to data structure - this link https://jimdehner2.blogspot.com/search/label/Zero%20to%20Zen will get you to my blog page and under session 1 you can download the workbook I used and also there is a link to the presentation recording - the part that speaks to your problem is about half way through the presentation and starts with Sparse Data)
Jim
