Calculate average withouot incorporating sub average

Hi, for my work I have to calculate the average of cases for a year and the data is set up with monthly average in the row. I also need to not incorporate the cells with 0 or a dash in them because they are referenced to another worksheet with formula. The data is set up this way:

dates Cases
1/08 1
1/15 3
1/25 4
1/30 2
1st period: =IF(SUM(B2:B5)>0,AVERAGE(B2:B5),0)
(average)
. ((the next 11 mos. are repeated)
.
.
12/1 -
12/8 -
12/16 -
12/23 -
12/30 -
12th prd: =IF(SUM(B76:B79)>0,AVERAGE(B76:79),0)
(average)

Average: (Average up to the date)


At the end, I want to calculate the average "up to date", but I'm having problem selecting range with just the subaverage cells because Excel won't let me. Can you please help me calculating the average up to date?

Thank you!

So are you wanting the average of all the averages?

Is so, is there anything in an adjacent cell to the subaverage that indicates that it is an average calculation?

Bob, fixed:

=IF(SUM(B2:B79)>0,AVERAGE(B2:B79),0)

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

Perhaps in B82:

=AVERAGE(IF(ISNUMBER($A$6:$A$80),IF(B$6:B$80<>0,B6:B80)))

confirmed with CTRL+SHIFT+ENTER not just ENTER and copied across.

You can change the formulas in B11:C11 and like cells to

=SUBTOTAL(1, B6:B10)

Then the formula in B82 is

=SUBTOTAL(1, B4:B80)

Note: I adjust formula above to average the non-subtotals, instead of the subtotals as per your original post.

Did you confirm it with CTRL+SHIFT+ENTER ? You should see { } brackets appear around the formula.

See attached:

You need to make sure the cell is active... hit F2, then confirm with CSE...

No, it is just warning you that the cell is not protected and than someone can overwrite it.

Excel is sometimes seemingly haphazard about displaying this.. it sometimes depends on what is in adjacent cells.. but just choose to ignore the error.

the "-" is because you formatted the cell to display a dash when you get a 0 result... which is incorrect for this formula... you have to use CSE keys to get right result.

You are welcome,

Please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

Then you want the averages of the Subaverages????

=AVERAGE(IF(ISNUMBER(SEARCH("PRD",$A$6:$A$80)),IF(B$6:B$80<>0,B6:B80)))

confirmed with CSE...

If not, please elaborate as to what you expect as a result and based on what

I am not a mathematician, but I think there will be a difference when you are averaging averages as opposed to averaging original numbers...

See here: http://wiki.answers.com/Q/Is_an_aver...rages_accurate

Can you find me that job please . I sure can't find it!