Adjust denominator for average calculation dynamically

[jaimonsk]

Hi Team,

Let's say there is a dataset for sales of cars for a year (Monthwise) as shown below. The idea is to generate Average column dynamically. The Average column needs to be found in such a way that if there are 2 or more than 2 months have no sale, then that month needs to considered as inactive. Therefore while counting the delimiter on taking average, we need to discard the inactive months. Ex: In row 1 the denominator is 10. Can someone help me with this logic?

Car	2018-1	2018-2	2018-3	2018-4	2018-5	2018-6	2018-7	2018-8	2018-9	2018-10	2018-11	2018-12	Average
X	0	0	10	20	30	10	30	40	20	50	5	0	21.5
Y	10	0	0	0	10	5	6	0	0	5	0	0	7.2
Z	0	0	0	0	0	0	0	0	0	0	20	10	15
A	0	0	0	130	0	0	0	0	0	0	0	0	130
B	0	0	0	0	0	0	0	0	0	0	15	0	7.5

[mceleavey]

@jaimonsk ,

I assume you meant 9 for row 1, not 10.

I built this using a bit of transpose and crosstab magic:

Hope this helps,

M.

[danilang]

hi @jaimonsk 

There's no single function to do this, since you have the 2-or-more criteria, but you can calculate this yourself

Transpose the values and use a multi-row tool to look for consecutive 0's.  Mark those rows with 0 and the all the rest with 1.  Sum the values and the marks and use the marks as the denominator.  

Dan

[jaimonsk]

Hi @mceleavey , @danilang Thanks for the reply. The earlier example i attached was wrong with the criteria i mentioned. I have updated the dataset accordingly and added a <bold> for every numbers which would be in scope. So for first row and last row. The zero at the end would need to get counted in denominator selection. which would alter the average calculation done earlier.
ie, the cell would be considered inactive only if there are 2 or more than 2 continuous cells are zero. In the mentioned case the zero would then need to get counted as active..
Sorry for the confusion!

[mceleavey · Accepted solution]

Hi @jaimonsk ,

I've applied that bit of scope creep 😉

Obviously now your averages are different as you are increasing the denominator value for the calc.

M.

[jaimonsk]

Superb 🙂
Thank you @mceleavey 

[mceleavey · Accepted solution]