I have a 10 X 10 block (simple I know) Columns C to L and Rows 3 to 12. In column M it calculates the STDV on its respective ROW and Column N calculates the EXTREME SPREAD of its ROW.
Then I want to catch the AVERAGE of both Columns M and N in Row 13. I use =AVERAGE(M3:N3) in cell N13. And it works even if there is an empty cell in the COLUMN.
However, using the same formula in CELL M13, it returns a #DIV/0! until all cells are populated. :-(
Why is that? Is their a way around it?
Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
I've tried to add the file
De-Primed Fired Cases. COAL measurement 10 times.
1 2 3 4 5 6 7 8 9 10 STDV ES
TrueX Caliper 1 1.4000 1.5000 0.0707 0.1000
2 1.2000 1.1000 1.4000 1.5000 1.6000 0.2074 0.5000
3 1.0000 2.0000 3.0000 1.0000 2.0000
4 0.7500 0.2500 0.3536 0.5000
5 1.5000 0.7500 0.5303 0.7500
6 1.2000 1.4000 0.1414 0.2000
7 0.8000 0.9000 0.0707 0.1000
8 0.7000 0.5000 0.1414 0.2000
9 0.3000 0.6000 0.2121 0.3000
10 0.4000 #DIV/0! 0.0000
Avg #DIV/0! 0.4650
Last edited by RustyRick; 11-22-2022 at 08:36 PM.
Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
If you want the average of Column M and the average of Column N Please try in M13 =AVERAGE(M3:M12) and copy to N13.I want to catch the AVERAGE of both Columns M and N in Row 13
If you want 1 average for both columns, please try =AVERAGE(M3:N12).
These formulas do not give DIV/0 if at least 1 cell in the specified range is filled in with a numeric value.
Last edited by HansDouwe; 11-23-2022 at 10:32 AM.
That is the formula I have used for years.
In cell M13 I have =AVERAGE(M3:M12) It works for column N in cell N13. But until all 10 cells in column M are filled, I get #DIV/0! The moment I complete all the cells with data in M, the calculation happens. I don't think I have ever seen this.
I'm going to try to add my file.
I'm trying to attach a file?
Da Da Whew I think I suceeded
STDEV(C12:L12) is causing a DIV/0 error that you formatted invisible.
That is causing a DIV/0 error in the average function AVERAGE (M3:M12)
Please try in M3 and copy down:Formula:
Please Login or Register to view this content.
Last edited by HansDouwe; 11-23-2022 at 04:31 PM.
test test test can deleted sorry
Thanks HansDouwe I still don't know what I did to cause the problem. Thanks a bunch!!
I don't know either, but you know bow how to solve the problem..
Thanks for the feedback and glad to have helped.
Also thanks for the rep
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks