Div/0 Error with average

Aixaix

New Member
Joined
Dec 25, 2019
Messages
7
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!
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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),"")
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
If I put 100 n B40 & 0 in E40 that formula returns 50
 
Upvote 0
Do you happen to have any errors in any of the other cells being referenced by your formula?
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
You will need to wrap those formulae in IFERROR as well
 
Upvote 0
=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: 11
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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
Back
Top