CODEWITHSUNDEEP
excel
Jeffrey Kramer
Jeffrey Kramer

Reputation: 1345

AVERAGEIF after operation within array formula

Say I have three columns of data:

A       B     C
2       10    Cat
3       12    Dog
2       13    Cat

I want average C for value Cat using:

=averageif(C:C,"Cat",B:B)

However, I would like to first perform an operation on column B and then calculate the average. I'm just drawing a blank on how to get the array to work. I want something like this:

{=averageif(C:C, "Cat", B:B/A:A*5)}

This of course doesn't work. I could make an extra column, but I would like to do this with a function if possible.

Is there a way to perform an array operation and then use AVERAGEIF, ie array manipulation and then calculate the average?

Upvotes: 0

Views: 245

Answers (1)

BrakNicku
BrakNicku

Reputation: 5991

You can use array version of AVERAGE (confirmed with Ctrl+Shift+Enter)

=AVERAGE(IF(C:C="Cat",B:B/A:A*5))

You should avoid using full column references in this version.

Upvotes: 1

Related Questions