# Average Formula Help

#### excel_training_wheels

##### Board Regular
Hi--

Anyone have a suggestion on how to calculate an average if the column currently is populated with the following? Column A has months, column B has numbers

Column A/B
Jan 32.4
Feb 21.8
Mar 19.7
Apr 51.6
May #DIV/0!
Jun #DIV/0!

There are formulas in the cells in column B which are calculating the numbers above... may and june return an error (which is ok...) I just want the average where the cell value does not equal #DIV/0!.

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

#### barry houdini

##### MrExcel MVP
Try either

=SUMIF(B1:B6,"<>#DIV/0!")/COUNTIF(B1:B6,"<>#DIV/0!")

or

=AVERAGE(IF(ISNUMBER(B1:B6),B1:B6))

confirmed with CTRL+SHIFT+ENTER

#### excel_training_wheels

##### Board Regular
Beautiful... the ISNUMBER function works great for me here.

Thanks!

Replies
3
Views
808
Replies
6
Views
319
Replies
0
Views
392
Replies
16
Views
674
Replies
3
Views
499

1,190,917
Messages
5,983,576
Members
439,852
Latest member
balasat

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