# AVERAGEIF #DIV_0! error if all cells are blank

#### Kumashin

##### New Member
Hi everyone.

I’ve been looking through so many topics here and many of them were really close to what I’m looking for as a solution but none of them no matter how many times I tried didn’t’ work out for my issue. Obviously I’m not an Excel guru.

I have a situation with Average formulas and many of them I copied from here do work but not in all cases. Basically I have up to 40 students and that number is not the same every day. What’s killing me is different number of students per day. Per instance today there was to be 32, 29 were present, 3 absent and the remaining 8 cells stayed blank. So cells contain ‘X’ which stands for Present, other letters represent absences (hospital, convoy escort, personal issues, on the mission etc…) and blank cells which stands for no students for that day. Now, I’ve managed to make some of the formulas to work for me but I cannot fix an issue where I have columns for days that are yet to come. These cell are blank and no universal formula that could be applied for all columns works. Statistically those columns should say 0% so other formulas can ease calculate weekly and monthly statistics excluding all 0% columns. But I simply cannot make them show 0%. I know it is me so after couple of days trying I gave up, time to call for higher authorities

I have an example attached and I am more than willing to assist in any way possible just to solve this out once and for all. In the attached file I have used many different formulas for the same task, sorry for that. The few cells are highlighted in red, to make it easier where I got stuck. L

By the way, it something like this was covered before than I really apologize, I didn’t find a solution here so far.

https://app.box.com/s/oi017hlbuflb2b14tx0w

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Example:

=IF(COUNTA(CT3:CT42),((COUNTA(CT3:CT42)-(COUNTIF(CT3:CT42,"<>X")-COUNTBLANK(CT3:CT42))))/(COUNTA(CT3:CT42)),0)

Awesome! Quick and spot on. Thanks Andrew, this really helps. I do appreciate your help.

Replies
7
Views
166
Replies
9
Views
401
Replies
6
Views
250
Replies
15
Views
462
Replies
0
Views
77

1,220,951
Messages
6,157,030
Members
451,392
Latest member
malcv

### 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