Am a little stuck with the average if function.
I want to average the values in column G (where populated) if Column A=PHA
It's monday afternoon! well,that's my excuse for being rubbish today!
Am a little stuck with the average if function.
I want to average the values in column G (where populated) if Column A=PHA
It's monday afternoon! well,that's my excuse for being rubbish today!
Last edited by AllenMead; 09-06-2010 at 12:46 PM.
Try like this
=AVERAGEIF(A:A,"PHA",G:G)
Audere est facere
OK, in that case you can use AVERAGEIFS (with an "S" at the end) and add another condition to exclude zeroes, i.e.
=AVERAGEIFS(G:G,G:G,">0",A:A,"PHA")
Do you want the average of the total of columns G and H? You won't be able to use AVERAGEIFS for something like that, you need an "array formula" like this
=AVERAGE(IF(A1:A100="PHA",IF(G1:G100+H1:H100>0,G1:G100+H1:H100)))
confirmed with CTRL+SHIFT+ENTER
No, I'm not explaining this very well (Can't post the sheet due to data protction act)
This is what I want but, it needs to check column E which must =PHA. If column E does not =PHA, then this must not be included.
=AVERAGEIF(Properties!BU3:BU350,">0",Properties!BV3:BV350)
I know this won't help, but the answer is -13.48 lol.
=AVERAGEIF(Properties!E3:E350,"PHA",Properties!BV3:BV350)
with this, I get -8.75
Last edited by AllenMead; 09-06-2010 at 12:13 PM.
I'm stupid, sorted. Thanks very much for your help.
Final equation;
=AVERAGEIFS(Properties!BV3:BV350,Properties!BU3:BU350,">0",Properties!E3:E350,"PHA")
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks