Ask Your Question
0

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

asked 2012-11-24 21:37:03 +0200

mechanicarts gravatar image

updated 2012-11-24 21:38:06 +0200

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 flag offensive 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

3 Answers

Sort by » oldest newest most voted
0

answered 2012-11-25 03:24:36 +0200

m.a.riosv gravatar image

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.

edit flag offensive delete link more

Comments

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

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

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

m.a.riosv gravatar imagem.a.riosv ( 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.

ROSt52 gravatar imageROSt52 ( 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.

m.a.riosv gravatar imagem.a.riosv ( 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.

ROSt52 gravatar imageROSt52 ( 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!

ROSt52 gravatar imageROSt52 ( 2012-11-26 03:20:47 +0200 )edit
0

answered 2012-11-25 02:55:00 +0200

horst gravatar image

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

edit flag offensive delete link more

Comments

this is very good news!!!

ROSt52 gravatar imageROSt52 ( 2012-11-25 03:02:24 +0200 )edit
0

answered 2012-11-25 01:32:39 +0200

ROSt52 gravatar image

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.

edit flag offensive delete link more

Question Tools

Stats

Asked: 2012-11-24 21:37:03 +0200

Seen: 1,612 times

Last updated: Nov 25 '12