Hi,
I have a problem with the averageif function.
My table layout is as follows....
Column "D" has dates on a daily basis extending three years down. Column "F" has percentages also extending down for each day represented in column "D". Cell K3 has a month in it which is dynamically changed. I needed to average column "F" on a per month basis and place that figure in cells K4-V4 under their repective months.
This code ({=AVERAGE(IF($D:$D,INT(K3),$F:$F))} works fine if there are NO ZEROS in the column..
Column "F" now contains Zeros and the above formula returns 0%. I have tried all sorts of variations that I have found on the web such as averageif, averageifs etc and the closest I can find is this one {=AVERAGE(IF($D:$D,INT(O3)),IF($F:$F>0,$F:$F))} which is returning a figure of 2852505% when it should be 70%
I hope someone can help ...
Are you saying you want to ignore zeroes ?
Assuming you are then given use of XL2007 you should be using AVERAGEIFS
AVERAGEIFS is far more efficient than an Array.K4: =AVERAGEIFS($F:$F,$D:$D,INT(K$3),$F:$F,"<>0") copied across to V4
If you use an Array you should restrict the precedent ranges as much as possible, ie:
The only real value to an Array would be backwards compatibility with XL2003 etc, however, you should note in that context entire column references are not permittedK4: =AVERAGE(IF($D$1:$D$2000=INT(K$3),IF(ISNUMBER(1/$F$1:$F$2000),$F$1:$F$2000))) confirmed with CTRL + SHIFT + ENTER copied across to V4
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks