3

I would like to know how to calculate variance (VAR, VARA, VARP) with criteria as in AVERAGEIF and AVERAGEIFS. The VARIF function does not seem to exist. This question also applies to others functions such as median, etc.

5

The FILTER function can be used to first select the parts of the range that meet your criteria. That result can then be passed to VAR (e.g. =VAR(FILTER(source_range, condition))).

FILTER can also be used to filter one column based on a condition on another column. For example, if you have columns:

x | 1
y | 2
x | 3

You can find the VAR for the rows that have x in the first column like this:

=VAR(FILTER(B:B, A:A = "x"))
  • The problem is that I want to apply the filter in one column and to the variance of another. For example if I have a table with 2 columns like this I want to filter for X then do variance with 1 and 7: x 1 z 2 x 7 – Legion May 24 '17 at 9:33
  • @Legion Updated the answer to include info on how to filter in one column and get the values from another column. You might also want to update your original question to be more specific. – Christopher Su May 24 '17 at 17:23
  • @Legion: That's important information to include in your question. Comments are meant to be temporary. – ale May 24 '17 at 21:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.