Average only the Sheet/Cell with data...

**WingNut** · 12-09-2006, 07:55 AM

New at the Excel thing, so forgive my lack of knowing.
I have 13 sheets 12 of them for each month (Jan-Dec) and Results sheet.
During each month the sheet get filled in with each day’s total credits and debits and sum them up for a monthly total and also give a difference of those totals. This works ok.
On the results sheet I would like to average the credits and debits and sum the month as they are filled. I can do this but not less the month not filled in yet. I am average them by all 12 even if there is no info. How can I exclude the month the totals are $0.00.
On the Results it now looks like this for credits and I change the C27 with D27 for debits. What can I change to not include the month/months were the totals are still $0.00.

=AVERAGE('January 2007'!C27,February!C27,March!C27,April!C27,May!C27 ,June!C27,July!C27,August!C27,September!C27,Octobe r!C27,November!C27,December!C27)

ps attached file also seems I put first put this in wrong forum , sorry.

Thank for any help!

**VBA Noob** · 12-09-2006, 08:13 AM

Hi,

Try this array (Ctrl + Shift + Enter).

As the summary page already ref the other sheets use range C11 to C22

=IF(ISERROR(AVERAGE(IF(C11:C22>0,C11:C22,""))),0,AVERAGE(IF(C11:C22>0,C11:C22,"")))

VBA Noob

**WingNut** · 12-09-2006, 08:38 AM

I see were you are going by using data on Referance sheet.
But I am getting an error with your array. Could be someting I am doing wrong.
Thanks again!

**VBA Noob** · 12-09-2006, 08:40 AM

Try this.

Let me know what error your getting if it still not working for you

VBA Noob

**WingNut** · 12-09-2006, 09:12 AM

Thank you that works.
Not sure what I done wrong to get the error but it was my bad.
I am not sure what your array all means but I will keeps studing on it.
The main thing for now is it works and you gave me a better idear for totaling the yearly bottom line.
Thanks!!