CODEWITHSUNDEEP
excel
4ndro1d
4ndro1d

Reputation: 2976

Calculate value based on condition in other column in Excel

I want to calculate the average of all row in column A where column B has the value 'same'

A   |  B
-----------
o.1 | same
0.2 | different
0.2 | same

The usual average for the whole data set looks like this:

=AVERAGE(Table8[A])

But I want something like this:

=AVERAGE(Table8[A] WHERE Table8[B] = 'same')

From the table above I need to get 0.15 as average.

EDIT: Additionally STDEV, QUARTILE and other functions should be usable as well

Upvotes: 0

Views: 340

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

Something like:

=SUMPRODUCT((A1:A100)*(B1:B100="same"))/COUNTIF(B1:B100,"same")

change the 100 to suit your needs.

Upvotes: 1

Related Questions