Hi, Michael very kindly assisted me with an average calculation which worked perfectly. I have since altered the structure of the column which now contains some zeros and I am baffled as cannot find a solution to average it correctly.
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 in column "D". Cell K3 has a month. I needed to average column "F" on a per month basis and place that figure in cells K4-V4 on a per month basis.
Michael gave me this code which works fine =AVERAGE(IF($D:$D,INT(K3),$F:$F)).
Now since I have modified Column "F" and it contains Zeros 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 ...
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 in column "D". Cell K3 has a month. I needed to average column "F" on a per month basis and place that figure in cells K4-V4 on a per month basis.
Michael gave me this code which works fine =AVERAGE(IF($D:$D,INT(K3),$F:$F)).
Now since I have modified Column "F" and it contains Zeros 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 ...