Problem with "Average" Calculation Formula

[Vinogamist]

Hi Brendan,

I’m in the middle of creating my “wine cellar” database. I created four “Rating” fields with “Number” type (see embedded screen shot). Some of the wines I have do not necessarily have scores/ratings from these 4 professional critics/magazines. Can I instruct Tap Forms to give me average rating/score based on the data/value I have provided? As you can see from the screen shot I have embedded, Tap Forms gave me the average rating from 4 critics even though I only entered values on 3 critics. Is Tap Forms smart enough not to include critic/s that was/were left blank?

I hope you can help.

Thanks
Ralé

Attachments:

You must be logged in to view attached files.

[Brendan]

What’s the formula you’re using? It’s probably treating the empty value as 0.

[Vinogamist]

I’m using the formula below because I can’t think of other possibilities.

(Rating ~ Wine Spectators + Rating ~ Wine Enthusiast + Rating ~ Wine Advocate + Wine & Spirits)/4

Some of the wines I have entered have rating from all 4 critics, but some only have 2 or 3 critic ratings.

I hope you can help, Brendan.

Thanks

Ralé

[Brendan]

Hi Rale,

Well the only way that would really work properly is if you added a separate Number field that you used as the divisor of the formula. Then when you set a rating, you’d have to set the divisor to the number of ratings you have. I know that’s ideal. Tap Forms does calculate averages the way you want when you use the Calculation Summary function, but that’s because it generates an array of values and it removes all null values before it performs the calculation.

Thanks,

Brendan

[Vinogamist]

Thanks Brendan.

Ralé

[Author]

Posts