Trying to remove 0's from Average summary

[Former Member]

I am trying to get an average of employee expenses. Some of the lines have a 0 amount. When I use the Summary function the 0 lines are included in the average. How do I exclude these? Example:

0

100

0

0

0

100

0

0

Average is 25 with the 0 lines. The average should be 100.

Thanks,

Rick

[Former Member · Accepted solution]

Try using running total based on the condition <>0 and select average in the field to summarize.

Regards,

Raghavendra

[Former Member · Accepted solution]

is it necessary to show employees with 0 expenses? if not use a records selection to get rid of the employees with a 0 value

or

do a sum summary on the employee expense so you will get 200 by your example

Create a formula that looks for amount that are greater than 0

Counter

if amount is <> 0 then

1

else

0

now do sum summary on the counter field which will give you 2 by your example.

now in another formula get your average

if sum summary on the counter field <> 0 then

sum summary on the employee expense / sum summary on the counter field

[Former Member · Accepted solution]

Raghavendra,

Thank you for the reply. I am not having much luck with this but will keep playing with it. I either get mo value or a value that makes no sense. I'll ley you know.

Rick

[Former Member · Accepted solution]

Zilla,

Thanks for the reply. I have not had a chance to try this yet. I will lwt you know.

Rick

[Former Member · Accepted solution]

This solved my problem.

Thank you very much.

Rick