# Translate from Excel: =AVERAGEIF(DataA,DataB,Condition) [closed]

I have this Excel function I need to translate. What it says is Calculate D3:D103 if for the specific cell the value in column A equals 1.

=AVERAGEIF(D3:D103,A3:A103,1)

The D column contains some medians, varying from 1 to 5 with 2 decimals, whereas A is only 1 or 2 (boy or girl).

I saw something with a SUMIF/COUNTIF, but I have to IF for another column's value!

edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2015-10-19 02:25:43.524106

Sort by » oldest newest most voted

I think you can get using a matrix formula:

=AVERAGEIF(D3:D103(A3:A103=1)(X3:X103=Z))

Enter the formula with Crl+Shift+Enter.

more

@mariosv Just for my curiosity, what is the part (A3:A103=1)(X3:X103=Z) doing?

( 2012-11-25 03:35:40 +0200 )edit

@ROSt52. Just an error, must be (A3:A103=1)*(X3:X103=Z)

( 2012-11-25 15:16:14 +0200 )edit

@mariosv Thanks for the * between. However, I don't get it. I interpret (A3:A103) as the condition that there is a 1 in the cells A3 to A103. Is this correct? But what is the function of (X3:X103)? Hope you don't mind my curiosity.

( 2012-11-26 02:41:22 +0200 )edit

@ROSt52 Forgive me, sometimes it is better goto to bed. The formula is =AVERAGE(D3:D103(A3:A103=1)). Adding "(X3:X103=Z)" if you want add a condition for X column and Z value.

( 2012-11-26 03:09:07 +0200 )edit

@mariosv Thanks for instant reply. Calc has the AVERAGEIF function already in the AVERAGE function. Great on one side but a problem for all the ones who don't know that. I would have never tried to add the condition. Hope this will be soon implemented as an AVERAGEIF.

( 2012-11-26 03:17:49 +0200 )edit

@mariosv Comments are short. With *(X3:X103) you can then add additional conditions. In the above example it would mean IF there is a 1 in A1:A103 AND in X3:X103. Non-consecutive areas. Great functionality. My curiosity is satisfied. Thanks!

( 2012-11-26 03:20:47 +0200 )edit

There is already an enhancement request here and someone is working on it.

more

this is very good news!!!

( 2012-11-25 03:02:24 +0200 )edit

I am surprised but I too don't find the AVERAGEIF function in Calc.This could be an enhancement request

I would build the function using AVERAGE (or AVERAGEA) and IF. This should be possible. You would need to look into details of your data.

more