How to get average of percentages that ignores #div/0! from non contiguous cells or ranges of cells

[specialedjen]

Hi, extreme excel novice here desperate for help. I'm trying to get an average of percentages from data that is broken into several different monthly tables. Therefore, sometimes it must pull from cells that are not together. For example, I have a worksheet for Student A wherein points are earned by period for every school day for certain expectations. At the end of each day an average is displayed as percent of total points earned by day. If a student is absent on a certain day, no average is calculated--thus the #div/0!. So....I'm trying to get an average for say November 27,28,29,30, and Dec 1.

November 27,28,29,30 are in the range of BV43:BV46 and then December 1st is in cell BV60. Student A was absent on Nov. 28 so #div/0! is displayed in cell BV44. The other days averages are 49%, 58%, 66%, and 70%. The percentage of points earned for Student A should be 60.75%. I want it to average only the four days with percentages in them and leave out the 5th date that is #div/0! rather than averaging the four percentages by 5.

I was able to figure out how to insert an =AVERAGEIF(BV36:BV39,"<>#DIV/0!") (for example) for contiguous ranges. I just can't figure out what to do about the non contiguous ranges. Any help would be greatly appreciated.

I apologize for long explanation....just trying to be as clear as possible.

 

[Fennek]

use the =aggregate() with the option "ignore errors"