How do I create an average that will exclude zeros?

PrevContributor
Previous Contributor

How do I create an average that will exclude zeros?

How can I do an average on numbers and exclude the zeros? For example, Year 1 = 10, Year 2 = 0, Year 3 = 4, therefore if ignore Year 2, average is 7 (and not 14 divided by 3)?

2 REPLIES 2
Mark_W_Shemaria
Super Contributor

RE: How do I create an average that will exclude zeros?

You will need to have an additional line item(s) which "filter" out the zeros and count the non zeros. Anaplan's average function will not natively exclude zeros in calculating the sum of items / number of items in the list(s).
tbeck
Certified Master Anaplanner

Re: How do I create an average that will exclude zeros?

1) Create a "Count" Line Item formatted as a Number

2) Formula = IF (line item) = 0 then 1 else 0 --> this will make a count for the denominator for the average. 

3) Create a line item for the calculation, here the Ratio summary can be used or the Sum of  Values / Count