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