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

#### specialedjen

##### New Member
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.

### Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

#### Fennek

##### Active Member
use the =aggregate() with the option "ignore errors"

Replies
3
Views
522
Replies
14
Views
2K
Replies
6
Views
2K
Replies
4
Views
3K
Replies
2
Views
994

1,133,525
Messages
5,659,327
Members
418,497
Latest member
VAllen79

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back