I am trying to use the following formula:
Formula:
=AVERAGEIF(P30:P67;1;AQ30:AS67)
So, I need to average on multiple columns, but the formula takes into account only 1st column of my average_range as if it was written like:
Formula:=AVERAGEIF(P30:P67;1;AQ30:AQ67)
How can I modify it or possible use {=AVERAGE(IF(.... } variant to account for multiple rows to calculate?
Last edited by Shurov; 12-11-2014 at 08:35 AM.
I think averageifs can accomplish that though I haven't used that function yet but if you have 2007 or later excel it is available. Give it a try and see.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
Unfortunately, no, it will not help. AVERAGEIFS takes care of multiple conditions, but single average_range that in AVERAGEIFS should be the same size as conditions_range.
While I need 1 conditions_range that is only 1-column-width and 1 average_range that is 5-columns-width.
Last edited by Shurov; 12-11-2014 at 08:39 AM.
ok, it was worth a shot. Good luck.
You need the array formula**:
=AVERAGE(IF(P30:P67=1,AQ30:AQ67))
Regards
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
I have tried it already, thanks. However, it returns some wrong values... they are different from AVERAGEIF gormula, however not the needed ones (when I check average manually). I couldn't found out, how they are calculated, yetOriginally Posted by XOR LX
you may need to post a sample spread sheet to get better suggestions.
Here is the sample book
Book1.xlsx
array function {=AVERAGE(IF...))} really works correct in this book. However, it fails to produce correct result when there are some missing values in the line with flag.
If you try to dele some values in 5th row, the formula will produce one result, while manual calculation of average on the needed row will produce another result.
How should I edit the function to omit empty cells?
I have tried to write smth like {=AVERAGE(IF(AND(A2:A6=1;C2:G6<>"");C2:G6))} but it returns zero value
You can't use AND, use this syntax
=AVERAGE(IF(A2:A6=1;IF(C2:G6<>"";C2:G6)))
confirmed with CTRL+SHIFT+ENTER
Audere est facere
that's it! thanks a lot!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks