LinkBack URL
About LinkBacks
Hello,
I was wondering if anybody knows how to ignore errors when using DAVERAGE?
Thanks for your help!
Hello,
I was wondering if anybody knows how to ignore errors when using DAVERAGE?
Thanks for your help!
I never used DAVERAGE but can't this be accomplished with AVERAGEIF function?
=AVERAGEIF(A2:A10,">0")
Table Average
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Hi There,
I'm using 'DAVERAGE' because I am using another column as criteria to determine what values to average. So unfortunately this won't work.
Thank you for your input though!
Well, without seeing your data the only thing I can offer is this link
http://www.techonthenet.com/excel/formulas/daverage.php
Example 1.xlsx
Hi!
Sorry, here is an attachment of my data. Please let me know if it didn't work. I would like average data from column G based on what year it was collected in. The averaged values are meant to be in columns N and O. It would be easy enough to just select which cells to average, however, I have 200 more tabs like this with differing amounts of 2013 and 2014 recordings so I would like to just make a macro to paste these new formulas in columns N and O and it calculate everything automatically regardless of errors.
Ps. The errors have been introduced in the data because the Estimated Flowrate is graphed after and I need it to show a blank rather than a 0.
Thank you for any help!
All you need is to add IFERROR to your formula in G.
=IFERROR(IF(OR(F3="Overflow",F3="Emptied"),NA(),IF(OR(F2="Overflow",F2="Emptied"),(3.785*(F3-0))/(A3-A2),IF(OR(A3="",F3=""),NA(),IF(OR(F2=F3,A2=A3),0,(3.785*(F3-F2))/(A3-A2))))),"")
But I need those errors in column G because in another spreadsheet I pull the data from column G and graph it and without the errors then my graph doesn't plot how I would like it to.
I see, ok,
Try this for 2013
=AVERAGEIFS(G2:G7,B2:B7,T2,G2:G7,">0")
For 2014
=AVERAGEIFS(G2:G7,B2:B7,U2,G2:G7,">0")
adjust ranges as need it.
Yes! That works... but I was just putting it into some of my other sheets and I've realized that I sometimes have negative numbers in column G! So that is going to screw up the ">0".... any way to get around that??
No problem, just replace >0 with "<>#N/A"
Oh right... I should have known that...
Thank you so much for your help today. I really appreciate it. It works perfectly!!
You're welcome and thank you for the feedback!
Last edited by AlKey; 06-10-2014 at 07:00 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks