+ Reply to Thread
Results 1 to 15 of 15

Average column of cells,but ignore errors and return average of numbers that are there...

  1. 06-14-2013, 07:22 PM #1
    synses
    synses is offline
    Registered User
    Join Date
    07-05-2012
    Location
    Florida US
    MS-Off Ver
    Excel 2007
    Posts
    96

    Average column of cells,but ignore errors and return average of numbers that are there...

    HI,
    E11 through E24 contains numbers and a few errors (#N/A) that need to persist (the errors need to show).

    E10 needs to show the average of the numbers that are in E11 through E24, and just ignore the errors.

    I have many columns like that - where the errors need to show and I need to show an average of the number/values that do appear, ignoring the errors.

    Is that possible? I tried a few older posts and nothing worked yet, any help appreciated greatly!

    Thanks,
    Synthia
    Reply With QuoteRegister To Reply
  2. 06-14-2013, 07:32 PM #2
    Pete_UK
    Pete_UK is offline
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,718

    Re: Average column of cells,but ignore errors and return average of numbers that are ther

    Try this array* formula in E10:

    =AVERAGE(IF(ISNUMBER(E11:E24),E11:E24))

    *An array formula needs to be confirmed using the key combination of Ctrl-Shift-Enter (CSE) instead of the usual <Enter>

    Hope this helps.

    Pete
    Reply With QuoteRegister To Reply
  3. 06-14-2013, 07:33 PM #3
    daddylonglegs
    daddylonglegs is offline
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Average column of cells,but ignore errors and return average of numbers that are ther

    Try using AVERAGEIF like this

    =AVERAGEIF(E11:E24,"<>#N/A")
    Audere est facere
    Reply With QuoteRegister To Reply
  4. 06-14-2013, 07:35 PM #4
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: Average column of cells,but ignore errors and return average of numbers that are ther

    DDL that will only work for that error type?
    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
    Reply With QuoteRegister To Reply
  5. 06-14-2013, 07:41 PM #5
    daddylonglegs
    daddylonglegs is offline
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Average column of cells,but ignore errors and return average of numbers that are ther

    Quote Originally Posted by FDibbins View Post
    DDL that will only work for that error type?
    Yes, specifically #N/A - if you want to exclude any error values then you could try either of these

    =AVERAGEIF(E11:E24,">0")

    which will work assuming all values are >0.....or for any values

    =AVERAGEIF(E11:E24,"<=9.9E+307")
    Reply With QuoteRegister To Reply
  6. 06-14-2013, 07:47 PM #6
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: Average column of cells,but ignore errors and return average of numbers that are ther

    Yes, the reason I posted that was because I tried it with #Ref!, but forgot to add the ! and wondered why it didnt work lol
    Reply With QuoteRegister To Reply
  7. 06-14-2013, 08:02 PM #7
    synses
    synses is offline
    Registered User
    Join Date
    07-05-2012
    Location
    Florida US
    MS-Off Ver
    Excel 2007
    Posts
    96

    Re: Average column of cells,but ignore errors and return average of numbers that are ther

    THANKS, I LOVE YOU FOLKS!
    Gratefully,
    Synthia
    Reply With QuoteRegister To Reply
  8. 06-14-2013, 08:05 PM #8
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Average column of cells,but ignore errors and return average of numbers that are ther

    If you were using Excel 2010 or later you could use this...

    =AGGREGATE(1,6,E11:E24)

    1 = average
    6 = ignore errors
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
  9. 06-14-2013, 08:16 PM #9
    synses
    synses is offline
    Registered User
    Join Date
    07-05-2012
    Location
    Florida US
    MS-Off Ver
    Excel 2007
    Posts
    96

    Re: Average column of cells,but ignore errors and return average of numbers that are ther

    OOps. I tried to use it for cells that "skip" cells, and got a "too many arguments message...?

    HTML Code: 
    ??
    Thanks,
    Synthia
    Reply With QuoteRegister To Reply
  10. 06-14-2013, 08:22 PM #10
    synses
    synses is offline
    Registered User
    Join Date
    07-05-2012
    Location
    Florida US
    MS-Off Ver
    Excel 2007
    Posts
    96

    Re: Average column of cells,but ignore errors and return average of numbers that are ther

    Thanks Biff, that worked on some cells, with but gave a #DIV/0! for =AGGREGATE(1,6,E20, H20, K20) becuase all three were #N/A. I need the #N/A? to persist and to be able to drag down the =AGGREGATE(1,6,E20, H20, K20) or =AVERAGEIF(E11, H11, K11,"<>#N/A")...I'll try =AVERAGEIF(E11, H11, K11,"<=9.9E+307")....
    Thanks,
    Synthia
    Reply With QuoteRegister To Reply
  11. 06-14-2013, 08:25 PM #11
    synses
    synses is offline
    Registered User
    Join Date
    07-05-2012
    Location
    Florida US
    MS-Off Ver
    Excel 2007
    Posts
    96

    Re: Average column of cells,but ignore errors and return average of numbers that are ther

    =AVERAGEIF(E20, H20, K20,"<=9.9E+307")

    gave a "too many arguments message...maybe I am not doing "skipping cells" correctly?

    Yes, I am using MC Excel 10 but leave Excel 2007 on my profile becuase I use that at work sometimes....
    Thanks,
    Synthia
    Reply With QuoteRegister To Reply
  12. 06-14-2013, 08:26 PM #12
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Average column of cells,but ignore errors and return average of numbers that are ther

    In that case you'd need to use an array formula**:

    =AVERAGE(IF(MOD(COLUMN(E11:K11),3)=2,IF(ISNUMBER(E11:K11),E11:K11)))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    Or, if there are only those 3 cells to calculate you could use this version:

    =AVERAGE(IF(COUNT(E11),E11,{""}),IF(COUNT(H11),H11,{""}),IF(COUNT(K11),K11,{""}))
    Reply With QuoteRegister To Reply
  13. 06-14-2013, 08:28 PM #13
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Average column of cells,but ignore errors and return average of numbers that are ther

    What result do you want when all cells contain the #N/A error?

    This will return a blank:

    =IFERROR(AGGREGATE(1,6,E11:E24),"")
    Reply With QuoteRegister To Reply
  14. 06-14-2013, 08:56 PM #14
    synses
    synses is offline
    Registered User
    Join Date
    07-05-2012
    Location
    Florida US
    MS-Off Ver
    Excel 2007
    Posts
    96

    Re: Average column of cells,but ignore errors and return average of numbers that are ther

    If the cells I am trying to average contain all #N/A I want a blank:
    I tried this:
    HTML Code: 
    AND IT WORKS!!!
    thank you so much!
    Synthia
    Reply With QuoteRegister To Reply
  15. 06-14-2013, 09:00 PM #15
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Average column of cells,but ignore errors and return average of numbers that are ther

    Good deal. Thanks for the feedback!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1