Zeros Averages and daverage

    Hi there.

    My boss has given me a great problem to sort out.

    He has a formula
    =IF(COUNTIF('Call Sheets'!$Y$7:$Y$692,L20)=0,0,DAVERAGE('Call Sheets'!A$7:AM$109,"curtatne",Formula!A1:B2))

    The problem is that it is also including any 0's in the average. eg, average of 7+0=3.5 and he wants it to exlcude the 0's.

    I can see what the formula is doing, and I read about using arrays, I just can't quite see how to apply them to the formula above. Can anyone suggest anything?

    I'm thinking I may need to create some hidden columns with seperate chunks of formula in to get what I need.

    Any help would be greatly appreciated.

    Many thanks
    Lisa

    Re: Zeros Averages and daverage


    I'm not familiar with DAVERAGE, and without an attachment I couldn't investigate it, but in general, averages excluding zeroes can be done with SUM or SUMIF, divided by COUNT or COUNTIF.

    Re: Zeros Averages and daverage


    Thanks for your help. I will go and try your suggestions.


    I have attached a copy of the document I'm trying to work on. (I didn't design it, I just have to use it). I think my boss is using the Daverage function, because of the possiblility of the column positions changing.


    The column giving me problems is F, on the Overview Sheet.


    The Overview sheet contains the report
    The Call Sheets contains the raw data
    The formula sheet contains some data he is using in his formulas.


    Again, any help is much appreciated.


    Thanks
    Lisa

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login