# 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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### Fennek

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Threads
1,167,923
Messages
5,856,304
Members
431,805
Latest member
mts91

### 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.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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