Averaging empty cells

Panorama X
1

I have a database with 3 fields. The first is a date field. Fields B and C are numeric. I enter a value in field B every day. I enter a value in field C every 6 days. When I Group Up by month, for example, and then average the summary records, field B is OK; but field C is about 1/6th the correct value. How can I average only the records which are not empty?

Thanks

2

This looks like a bug, because the documentation says there is no change from Panorama 6, but Panorama 6 didn’t do that.

You could

field C
select C > 0 or info("summary") > 0
average
3

Thanks Dave, but I like to use the Summary Table and Chart.

4

I don’t know if you can use it, but the aggregate function can give you an average of visible non-zero cells:

aggregate({C},"average",{C>0})
5

If the averaged field could contain negative and/or zero values, but you want to exclude only truly empty cells a better formula would be:

aggregate({C},"average",{emptycell(C)=false()})
6

Good point.

7

There is an easy way to do this in the Summary Workshop. First, use the Find/Select dialog to set up a favorite search for non-empty values in the field. In your case, the search would be C Is Not Empty. Be sure to save that as a favorite search.

Then, in the Summary Workshop, use the Data popup to set the data source to the favorite search you set up. Now the average will include only the non-empty values.

8

Would that not eliminate 5 out of six values from field A? I would like to compare the monthly average of 30 or so values in field A to the monthly average of 5 or so values in field B.

9

Sorry, that is not currently possible.