Hi there, see if anybody out there could help me with supress or remove #div/o! error, i have this formula that remove the highest and lowest values and average the middle 3 nunmbers out of five on col H26 every time y add the 2nd value to one of the five values the #DIV/0! ERROR appears, is there is a way to stop showing this erroR everytime i add the 2nd value out of the five, many thanks in advance for any help.
I have attached a workbook with a example for a better reference
I used this in sheet1 E23. you can use the same principle on any of the other calcs...
=IF(ISERROR(SUM(LARGE(H26:H35,{1,2}))),"",SUM(LARGE(H26:H35,{1,2})))
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
If you are going to average C26:G26 with excluding the min and max, wouldn't you need at least three numbers?
In H26, maybe...
=IF(COUNT(C26:G26)>2,(SUM(C26:G26)-MIN(C26:G26)-MAX(C26:G26))/(COUNT(C26:G26)-2),0)
HTH
Regards, Jeff
Note that TRIMMEAN function can be used to average without the top and bottom values, i.e.
=TRIMMEAN(C26:G26,2/COUNT(C26:G26))
....so you could use this version to prevent errors
=IFERROR(TRIMMEAN(C26:G26,2/COUNT(C26:G26)),0)
Last edited by daddylonglegs; 11-17-2012 at 09:21 PM.
Audere est facere
Many thanks to Fdibbins, Jeffreybrown and daddlylonlegs, all formulas supress the #div/o! error, but was Jeffreybrown formula that nailed, thanks to all of you for taking of your time to help with this formula, really appreciated. Cheers
happy to help and 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