Hello,
how can I calculate the weighted average?
Example:
[-4, 0, 2, 0] -> I get this values via a roolup
I want the result to be -2 i.g.. Correct me please if I am wrong.
I'm open to use multiple formulas.
Best regards
Johannes
Solved
Calculating the weighted average
Best answer by stevendasilva
Just to confirm are you looking for the average but only if the result is not 0?
If yes this has been solved over here:
https://community.airtable.com/t5/formulas/excluding-zero-values-from-an-average-rollup/td-p/26312
Just to confirm are you looking for the average but only if the result is not 0?
If yes this has been solved over here:
https://community.airtable.com/t5/formulas/excluding-zero-values-from-an-average-rollup/td-p/26312
Hi @stevendasilva ,
thanks. I already saw the post. While reading the message more closely, I found out that it actually is the solution for my problem. However, there is no example, so I didn't figure it out the first time.
My solution was to return BLANK() instead of a 0, when the Formula encountered a BLANK() at the first or second value. If this is done, the AVERAGE() function of the Rollup skips these values.
Example:
- Values is the first table:
- Data: The data are either blanks or numbers from 1 to infinite. They represent "bad" and "good" occurrences.
- Formula: IF(AND({👍}=BLANK(), {👎}=BLANK()), BLANK(), IF({👍}=BLANK(),-{👎},IF({👎}=BLANK(),{👍},{👍}/({👎}+{👍}))))
- Rollup in the other table: IF(COUNTA(values)!=0, AVERAGE(values), BLANK())
Best regards
Johannes
Reply
Most helpful members this week
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.