Average formula that shows 0 but does not count in average

Hi, I need some help with a dashboard I have created in the file attached, on the data tab in the week to date column I have a formula that calculates the average for each week on a rolling basis for that week ) so updates the week average as new data is entered for each day).

This is shown on a speed dial on A I tab, my problem is that if I enter 0 for a days A I in data tab for the first day of the week, the week column does not pick it up and the speed dial still shows the last number above 0 from the previous week, but if I add the Iferror at the start of my formula it shows the 0 until the end of the year and the speed dial picks that up.

I would like the week to date column to display o from the last 24hr column and show on speed dial but not count 0 in the average in the week to date column - if this is possible please. Hope this is clear.
Password is dashboard
Thanks
Lewster

Can you be a bit more specific about EXACTLY where the problem is? What cell(s) contain the problem formula and finally, unlock the sheet so that we can select the cell(s) and see what the formula is!!!!!!!

I'm not sure if I fully understand what you want, but try:

=IF(C5="","",IF(C5=0,0,AVERAGEIFS($C$5:C5,$C$5:C5,">0")))

Great! I'm glad to have helped! If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

OK... Still not clear - so another stab in the dark!!

=IF(C5="","",IF(C5=0,IF(ISNUMBER(D4),D4,0),AVERAGEIFS($C$5:C5,$C$5:C5,">0")))

This works to some extent,
Could you please explain the formula? Do not understand why D4 is there as D4 is text and if I change to D5 I get a circular reference warning ---(ISNUMBER(D4),D4,0), then when I go into the next week D12 the ISNUMBER references to D11 and puts number in D11 into D12??

It was done to take account of the first cell in the column. If instead of IF(ISNUMBER(D4),D4,0) you just have D4, it copies the number from above if there's a zero on column C. That's fine, unless the entire column C starts with a zero. In that particular case it would copy the column header into D, which is incorrect!! Instead, the formula copies the number from the cell above, providing what is in the cell above is actually a number or zero, if it isn't.