problems using subtotals for averages and median issues

[surbannomad]

Hi Everyone,

Learnt so much from the site and the advice you guys give..but cound'nt find an answer to a problem that I am facing....here goes

I have a column numbers of staff. I am currently using

=SUMPRODUCT(('SE A+B + CODED'!$AP:$AP>=1)*('SE A+B + CODED'!$AP:$AP<=5)*(SUBTOTAL(3,OFFSET('SE A+B + CODED'!$AP:$AP,ROW('SE A+B + CODED'!$AP:$AP)-MIN(ROW('SE A+B + CODED'!$AP:$AP)),0,1))))

=SUMPRODUCT(('SE A+B + CODED'!$AP:$AP>=1)*('SE A+B + CODED'!$AP:$AP<=5)*(SUBTOTAL(103,OFFSET('SE A+B + CODED'!$AP:$AP,ROW('SE A+B + CODED'!$AP:$AP)-MIN(ROW('SE A+B + CODED'!$AP:$AP)),0,1))))

which work well and produce the counts figures that I require using the multiple criteria...no problem showing the hidden and visisble, and visible only counts. Problem I am facing is that I need to do average staff per range on the same column and median staff per range. In case of averages I have used:

=SUMPRODUCT(('SE A+B + CODED'!$AP:$AP>=1)*('SE A+B + CODED'!$AP:$AP<=5)*(SUBTOTAL(1,OFFSET('SE A+B + CODED'!$AP:$AP,ROW('SE A+B + CODED'!$AP:$AP)-MIN(ROW('SE A+B + CODED'!$AP:$AP)),0,1))))

=SUMPRODUCT(('SE A+B + CODED'!$AP:$AP>=1)*('SE A+B + CODED'!$AP:$AP<=5)*(SUBTOTAL(101,OFFSET('SE A+B + CODED'!$AP:$AP,ROW('SE A+B + CODED'!$AP:$AP)-MIN(ROW('SE A+B + CODED'!$AP:$AP)),0,1))))

but I am not getting any values back only getting #DIV appearing in cells. Need your help on this formuale

For Median I am informed by your site that I can use subtotals as per range.... any suggestions for work around....still using subtotal inside statement as I need to difefrentiate from visible and hidden + visible your guidance and help would be gratefully appreciated.......