+ Reply to Thread
Results 1 to 18 of 18

how to average large formulas ignoring 0 and blank

  1. #1
    Registered User
    Join Date
    11-25-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    99

    how to average large formulas ignoring 0 and blank

    So in Cell K12 there is a rather large formula (I condensed it for this example).

    This formula is to average out the respective cells in column J. For each cell there is a possibility of 9 different entries. NRT, N/O, 1,2,3,4,5,6,7

    So based on the formula in K12 I need Cell J12 to Display the answer.

    if any one of the Cells referenced in the formula are NRT then I need it to Display NRT. Which the Formula does now.

    The problem comes in when a Cell is N/O (Not Observed)

    How do I get the formula to Ignore N/O currently I have it set up to recognize N/O as 0. But excel averages 0 in and it affects the answer.

    I know in a simple formula I can add <>0 to the formula to ignore zeros. what I cannot figure out is how to write it into a bigger formula such as in K12.

    For example the current numbers in Cells J17:J25 should average 5 however the formula averages it as 1 because it calculates N/O as 0.
    Attached Files Attached Files

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: how to average large formulas ignoring 0 and blank

    Hi,

    =IF(COUNTIF(J17:J25,"NRT"),"NRT",AVERAGE(J17:J25))

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: how to average large formulas ignoring 0 and blank

    Try it like this...

    =IF(OR(J17="NRT",J19="NRT",J21="NRT",J23="NRT",J25="NRT"),"NRT",IFERROR(AVERAGE(J17,J19,J21,J23,J25),""))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: how to average large formulas ignoring 0 and blank

    Or this

    =IFERROR(IF(LOOKUP(2,1/(J17:J25="NRT")),"NRT"),AVERAGE(J17:J25))
    Last edited by AlKey; 06-20-2014 at 10:51 AM.
    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

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: how to average large formulas ignoring 0 and blank

    If the cells between the data cells will always be empty then this shorter array formula** will do:

    =IFERROR(IF(OR(J17:J25="NRT"),"NRT",AVERAGE(J17:J25)),"")

    ** 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.

  6. #6
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: how to average large formulas ignoring 0 and blank

    Quote Originally Posted by Tony Valko View Post
    If the cells between the data cells will always be empty then this shorter array formula** will do:

    =IFERROR(IF(OR(J17:J25="NRT"),"NRT",AVERAGE(J17:J25)),"")

    ** 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.
    Tony - why use an array construction with OR?

    You know that I'm not one of those who is "against" array formulas for the sake of it (wonderful things, in my opinion), but surely a simple COUNTIF is sufficient here?

    Regards

  7. #7
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: how to average large formulas ignoring 0 and blank


  8. #8
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: how to average large formulas ignoring 0 and blank

    Quote Originally Posted by XOR LX View Post
    Tony - why use an array construction with OR?
    Just to be different!

    The COUNTIF version is slightly faster to calculate on large data ranges but on a small range like this there's no difference.

  9. #9
    Registered User
    Join Date
    11-25-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    99

    Re: how to average large formulas ignoring 0 and blank

    Quote Originally Posted by Tony Valko View Post
    If the cells between the data cells will always be empty then this shorter array formula** will do:

    =IFERROR(IF(OR(J17:J25="NRT"),"NRT",AVERAGE(J17:J25)),"")

    ** 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.
    Sorry I failed to mention the cells between will contain similar data averaged out in the same way it is just a different category to average out. This is why the formula is long and each cell is identified in the formula.

  10. #10
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: how to average large formulas ignoring 0 and blank

    OK, then the "long" version in post #3 will do what you want.

  11. #11
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: how to average large formulas ignoring 0 and blank

    Quote Originally Posted by Tony Valko View Post
    Just to be different!

    The COUNTIF version is slightly faster to calculate on large data ranges but on a small range like this there's no difference.
    I can live with that! Variety at the cost of a few milliseconds is not at all a bad thing in Excel!

  12. #12
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: how to average large formulas ignoring 0 and blank

    Just answered a "why use an array formula" a few minutes ago:

    http://www.excelforum.com/excel-form...umproduct.html

  13. #13
    Registered User
    Join Date
    11-25-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    99

    Re: how to average large formulas ignoring 0 and blank

    So the long formula work which Valko posted.

    My actual formula now looks like what I have posted below it works beautifully. Thanks!!!

    Someone posted the possibility of a COUNTIF formula to speed up calculations. Any thoughts on this for this formula. I have 14 of these formulas in one work sheet.

    =IF(OR(J35="NRT",J45="NRT",J55="NRT",J65="NRT",J75="NRT",J85="NRT",J95="NRT",J105="NRT",J115="NRT",J125="NRT",J135="NRT",J145="NRT",J155="NRT",J165="NRT",J175="NRT",J185="NRT",J195="NRT",J205="NRT",J215="NRT",J225="NRT",J235="NRT",J245="NRT",J255="NRT",J265="NRT",J275="NRT",J285="NRT",J295="NRT",J305="NRT",J315="NRT",J325="NRT",J335="NRT",J345="NRT",J355="NRT",J365="NRT",J375="NRT",J385="NRT",J395="NRT",J405="NRT",J415="NRT",J425="NRT"),"NRT",IFERROR(AVERAGE(J35,J45,J55,J65,J75,J85,J95,J105,J115,J125="N/O",0,J125,J135,J145,J155,J165,J175,J185,J195,J205,J215,J225,J235,J245,J255,J265,J275,J285,J295,J305,J315,J325,J335,J345,J355,J365,J375,J385,J395,J405,J415,J425), ""))

  14. #14
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: how to average large formulas ignoring 0 and blank

    Quote Originally Posted by nsmjc View Post

    =IF(OR(J35="NRT",J45="NRT",J55="NRT",J65="NRT",J75="NRT",
    J85="NRT",J95="NRT",J105="NRT",J115="NRT",J125="NRT",
    J135="NRT",J145="NRT",J155="NRT",J165="NRT",J175="NRT",
    J185="NRT",J195="NRT",J205="NRT",J215="NRT",J225="NRT",
    J235="NRT",J245="NRT",J255="NRT",J265="NRT",J275="NRT",
    J285="NRT",J295="NRT",J305="NRT",J315="NRT",J325="NRT",
    J335="NRT",J345="NRT",J355="NRT",J365="NRT",J375="NRT",
    J385="NRT",J395="NRT",J405="NRT",J415="NRT",J425="NRT"),"NRT",IFERROR(AVERAGE(J35,J45,J55,J65,J75,J85,J95,
    J105,J115,J125="N/O",0,J125,J135,J145,J155,J165,J175,J185,
    J195,J205,J215,J225,J235,J245,J255,J265,J275,J285,J295,J305,
    J315,J325,J335,J345,J355,J365,J375,J385,J395,J405,J415,J425), ""))
    We can probably shorten that a bit but this part inside the AVERAGE function looks odd:

    J125="N/O",0

  15. #15
    Registered User
    Join Date
    11-25-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    99

    Re: how to average large formulas ignoring 0 and blank

    Yes I caught that I edited it in word and must have caught it in when I pasted asked it into excel and was getting the wrong answer. Thanks for all of your help.

    Not sure how to shorten it to much when I need all of the cells which are not consecutive.
    The formula is in between each row just under a different catagory. The long formula does make excel run a little leggy on some computers.

  16. #16
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: how to average large formulas ignoring 0 and blank

    You can replace this long formula...

    =IF(OR(J35="NRT",J45="NRT",J55="NRT",J65="NRT",J75="NRT",
    J85="NRT",J95="NRT",J105="NRT",J115="NRT",J125="NRT",
    J135="NRT",J145="NRT",J155="NRT",J165="NRT",J175="NRT",
    J185="NRT",J195="NRT",J205="NRT",J215="NRT",J225="NRT",
    J235="NRT",J245="NRT",J255="NRT",J265="NRT",J275="NRT",
    J285="NRT",J295="NRT",J305="NRT",J315="NRT",J325="NRT",
    J335="NRT",J345="NRT",J355="NRT",J365="NRT",J375="NRT",
    J385="NRT",J395="NRT",J405="NRT",J415="NRT",J425="NRT"),
    "NRT",IFERROR(AVERAGE(J35,J45,J55,J65,J75,J85,J95,
    J105,J115,J125="N/O",0,J125,J135,J145,J155,J165,J175,J185,
    J195,J205,J215,J225,J235,J245,J255,J265,J275,J285,J295,J305,
    J315,J325,J335,J345,J355,J365,J375,J385,J395,J405,J415,J425), ""))
    With this array formula**:

    =IF(SUMPRODUCT(--(MOD(ROW(J35:J425)-ROW(J35),10)=0),--(J35:J425="NRT")),"NRT",IFERROR(AVERAGE(IF(MOD(ROW(J35:J425)-ROW(J35),10)=0,J35:J425)),""))

    Assumes no empty cells. If there might be empty cells then use this array formula**:

    =IF(SUMPRODUCT(--(MOD(ROW(J35:J425)-ROW(J35),10)=0),--(J35:J425="NRT")),"NRT",IFERROR(AVERAGE(IF(MOD(ROW(J35:J425)-ROW(J35),10)=0,IF(J35:J425<>"",J35:J425))),""))

    ** 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.

  17. #17
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: how to average large formulas ignoring 0 and blank

    Quote Originally Posted by Tony Valko View Post

    If there might be empty cells then use this array formula**:

    =IF(SUMPRODUCT(--(MOD(ROW(J35:J425)-ROW(J35),10)=0),--(J35:J425="NRT")),"NRT",IFERROR(AVERAGE(IF(MOD(ROW(J35:J425)-ROW(J35),10)=0,IF(J35:J425<>"",J35:J425))),""))
    Ouch! SUMPRODUCT in an array formula?!
    Last edited by XOR LX; 06-23-2014 at 02:08 AM.

  18. #18
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: how to average large formulas ignoring 0 and blank

    At least I didn't try to muck it up with a bunch of INDEX functions!

    Array entered**:

    =IF(SUM(IF(MOD(ROW(J35:J425)-ROW(J35),10)=0,IF(J35:J425="NRT",1))),"NRT",IFERROR(AVERAGE(IF(MOD(ROW(J35:J425)-ROW(J35),10)=0,J35:J425)),""))

    =IF(SUM(IF(MOD(ROW(J35:J425)-ROW(J35),10)=0,IF(J35:J425="NRT",1))),"NRT",IFERROR(AVERAGE(IF(MOD(ROW(J35:J425)-ROW(J35),10)=0,IF(J35:J425<>"",J35:J425))),""))

    ** 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.


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Sumproduct Average ignoring blank cells
    By fgruhlke in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-13-2014, 12:32 PM
  2. Getting average but ignoring cells with blank (#value!)
    By Elainefish in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-23-2013, 08:30 AM
  3. average %'s between sheets (ignoring blank)
    By simpson in forum Excel General
    Replies: 0
    Last Post: 03-09-2010, 03:19 PM
  4. [SOLVED] 30 Day Moving Average Ignoring Blank Cells
    By ethatch in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-17-2006, 05:40 AM
  5. [SOLVED] Ignoring Blank/Empty Cells that contain formulas
    By pabown in forum Excel General
    Replies: 4
    Last Post: 01-25-2005, 06:06 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1