# 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?

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

@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.

@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.

@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.

@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! There is already an enhancement request here and someone is working on it.

more 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