Div/0 Error with average

Aixaix

New Member
Joined
Dec 25, 2019
Messages
6
Office Version
  1. 365
Platform
  1. MacOS
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!
 

Some videos you may like

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,749
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
55,804
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 25, 2019
Messages
6
Office Version
  1. 365
Platform
  1. MacOS
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
Joined
Jun 12, 2014
Messages
55,804
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,749
Office Version
  1. 365
Platform
  1. Windows
Do you happen to have any errors in any of the other cells being referenced by your formula?
 

Aixaix

New Member
Joined
Dec 25, 2019
Messages
6
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,749
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
55,804
Office Version
  1. 365
Platform
  1. Windows
You will need to wrap those formulae in IFERROR as well
 

Aixaix

New Member
Joined
Dec 25, 2019
Messages
6
Office Version
  1. 365
Platform
  1. MacOS
=AVERAGE(B2:B39)
=AVERAGE(C2:C39)

all the way up to AZ
 

Attachments

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

Watch MrExcel Video

Forum statistics

Threads
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.
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