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

specialedjen

New Member
Joined
Dec 1, 2016
Messages
1
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.

Watch MrExcel Video

Forum statistics

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

Which adblocker are you using?

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
Top