# Div/0 Error with average

#### Aixaix

##### New Member
Good afternoon!
I am trying to get an excel formula to function correctly. I currently have 3 rows of numbers per salesperson that average at the bottom of each row. I am then trying to average the rows for every 3rd row into another cell so if I remove a salesperson I want to see what the average would increase or decrease. The issue is not every salesperson has data in each row so its throwing out the div/0 error. I have attempted several different formulas to see if I can get it it working but some still give the div/0 and some return no data.

=IFERROR(AVERAGE(B40,E40,H40,K40,N40,Q40,T40,W40,Z40,AC40,AF40,aj40i40,AI40,AL40,AO40,AR40,AU40,AX40),"")
=AVERAGE(B40,E40,H40,K40,N40,Q40,T40,W40,Z40,AC40,AF40,AJ40,I40,AI40,AL40,AO40,AR40,AU40,AX40)

Any help would be great. Thank you!

### Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

#### Joe4

Your first formula should work, but it looks like you have a typo in it:
Rich (BB code):
``=IFERROR(AVERAGE(B40,E40,H40,K40,N40,Q40,T40,W40,Z40,AC40,AF40,aj40i40,AI40,AL40,AO40,AR40,AU40,AX40),"")``

#### Fluff

##### MrExcel MVP, Moderator
Maybe
Rich (BB code):
``=IFERROR(AVERAGE(B40,E40,H40,K40,N40,Q40,T40,W40,Z40,AC40,AF40,AJ40,I40,AI40,AL40,AO40,AR40,AU40,AX40),"")``
although the two cells in red seem to be wrong

#### Aixaix

##### New Member
Maybe
Rich (BB code):
``=IFERROR(AVERAGE(B40,E40,H40,K40,N40,Q40,T40,W40,Z40,AC40,AF40,AJ40,I40,AI40,AL40,AO40,AR40,AU40,AX40),"")``
although the two cells in red seem to be wrong
=IFERROR(AVERAGE(B40,E40,H40,K40,N40,Q40,T40,W40,Z40,AC40,AF40,AI40,AL40,AO40,AR40,AU40,AX40),"")

Good catch. I have updated the formula with the updated cells. in b40 it gives me 50.00 (which is correct based on 100 and 0 for the average) but now a3(Where the =IFERROR is going) gives me blank data. I would like this to reflect 50.00 (based on the only data available in the cells selected.

#### Fluff

##### MrExcel MVP, Moderator

If I put 100 n B40 & 0 in E40 that formula returns 50

#### Joe4

Do you happen to have any errors in any of the other cells being referenced by your formula?

#### Aixaix

##### New Member

If I put 100 n B40 & 0 in E40 that formula returns 50
I also have cell b40 using the =average(b2:b39)

I am showing the Div/0 Error in the other cells as there is no data populated at this time.

#### Joe4

I am showing the Div/0 Error in the other cells as there is no data populated at this time.
If you try averaging any cells with errors in them, that will also result in an error.
What exactly are the formulas in those cells?
Can you rewrite them, applying the IFERROR function around them to handle those errors?

#### Fluff

##### MrExcel MVP, Moderator
You will need to wrap those formulae in IFERROR as well

#### Aixaix

##### New Member
=AVERAGE(B2:B39)
=AVERAGE(C2:C39)

all the way up to AZ

#### Attachments

• Screen Shot 2021-01-20 at 2.59.44 PM.png
26.7 KB · Views: 2

Replies
5
Views
447
Replies
1
Views
169
Replies
7
Views
404
Replies
2
Views
613
Replies
0
Views
288

1,128,165
Messages
5,629,068
Members
416,363
Latest member
zaveedd

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