Exclude Zeros From Average

I am calculating an average but I do not want the zero values to be included in the average. How can I exclude them?

Accepted answers

You can do this by adding a line item to count the values you want to include, i.e.: add a line item 'Count' = If 'Value' <> 0 then 1 else 0. Then you can calculate the average with a formula that is 'Value' / 'Count'. You will need to change the summary type of this line item to either 'Formula' or 'Ratio'. If you choose ratio, you need to specify the Numerator / Denomator below, so you would select 'Value' / 'Count' in this case.

All comments

PrevContributor

lokeshsinghal70

How to SUM the last 4 non zero values in the time series???

Example - if i have a data as -

12,0,4,0,8,12,9,0,5,0,3,5,7,0,3

Answer should be 18

12,0,4,0,8,12,9,0,5,0,3,0,7,3,3

Answer should be 16

Any ideas????

bhill5

not entirely elegant but....you'll need to use a "signal row" = if number row <> 0 then 1 else 0….next add calc row that divides ‘number row’ by "signal row" (the count)…and in blueprint mode change the calc row from sum to ‘formula’.

See attached pictures

Screen_Shot_2019-07-01_at_12_48099.png

Quick Links

Can you help?

Unanswered questions

Forecaster missing Error Messages and Stuck on Creation
Hi! I'm currently trying to set up an example for the forecaster and I'm running into various issues. At first (and I managed to resolve it by now) my data collection creation failed without any error messages. Next, after I managed to create it, my forecaster model is stuck on status 'creating' for 1.5 hours already. Is…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…
Top-Down Allocations
Estimated Level of Effort:4-8 Hours of Model Building Level of Difficulty: Beginner Recommended Training: L2 Model Builder Training Persona: Casual Model Builder Potential ROI: Decreased planning time Increased accuracy of targets/spreads You Might Also Like: * Historical Snapshotting Top-Down Allocations Whether it's…