Ask Your Question

CALC. How to apply a function in cells where another cell in the row has a specific value [closed]

asked 2018-11-15 11:25:50 +0200

kiko35 gravatar image

updated 2020-08-09 20:09:56 +0200

Alex Kemp gravatar image

Hi! Sorry if this was already asked. I know it's very basic, but I'm not able to solve it and didn't find a post about it.

I want to know the AVERAGE of the cells in column 'value' depending on the 'name'. Until now, I used to order column 'Name', so =AVERAGE(B1:B3) for µ and B4:B5 for ß, but I need to order my data constantly using other columns. Therefore, I would like to know how to select cells depending of the value of another cell in the same row. Something like AVERAGE in B1:B5 (the whole spreadsheet) WHERE column A = µ.

Could you help me? Thanks!

µ         50
ß         90
µ         10
µ         7
ß         65
edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by kiko35
close date 2020-04-01 20:15:07.689444

1 Answer

Sort by » oldest newest most voted

answered 2018-11-15 14:01:14 +0200

Lupp gravatar image

updated 2018-11-15 14:16:27 +0200

=AVERAGEIFS(B1:B5;A1:A5;"µ") or even
=AVERAGEIFS(B:B;A:A;"µ") if there is nothing else but your criterion strings in column A.
AVERAGEIFS() accepts many pairs of a range and a criterion. The range of values to take the average of is given as the first parameter.
There is also the function AVERAGEIF(), but I would deprecare it because the range of averages is in the last place there.
Howver, AVERAGEIFS() is not available in very old versions. Also old versions and still the AOO don't accept the complete-column-notation like A:A.

There are the respective multiple-criteria-functions SUMIFS() and COUNTIFS(), but to get applied an arbitrary function under a criterion-like condition requires explicit constructs, and most likely the forcing of array-evaluation. In some cases the SUMPRODUCT() function can help to find a workaround.

edit flag offensive delete link more


Thank you!!

kiko35 gravatar imagekiko35 ( 2018-12-13 10:24:37 +0200 )edit

Question Tools

1 follower


Asked: 2018-11-15 11:25:50 +0200

Seen: 681 times

Last updated: Nov 15 '18