+ Reply to Thread
Results 1 to 11 of 11

Average without counting not used cells?

  1. 07-22-2013, 06:45 AM #1
    Vihral
    Vihral is offline
    Forum Contributor
    Join Date
    12-18-2012
    Location
    Sweden
    MS-Off Ver
    Office 2016
    Posts
    158

    Average without counting not used cells?

    Hi,

    Got an =Average in Cell I10 which calulates the values from I3 to I8.

    However these values are always stated as 5 until the conditions in B-H has been altered. Can I make the average formula disregard the value in I3 to I8 if no text or value has been added in B3 to B8 (For example, informationm has been added in B3, B4 and B6, but not the others, can the average formula in someway disregard the others then and only count if the B column has a value in it?)

    Thanks in advance and hope you understand what i mean (If there are anything unclear let me know and ill upload a sample file.
    Reply With QuoteRegister To Reply
  2. 07-22-2013, 06:52 AM #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,733

    Re: Average without counting not used cells?

    You can use this:

    =AVERAGEIF(B3:B8,">0",I3:I8)

    Hope this helps.

    Pete
    Reply With QuoteRegister To Reply
  3. 07-22-2013, 06:54 AM #3
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Average without counting not used cells?

    Do you mean?

    =AVERAGEIF(B3:B8,">0",i3:i8)
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

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

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.
    Reply With QuoteRegister To Reply
  4. 07-22-2013, 06:58 AM #4
    Pepe Le Mokko
    Pepe Le Mokko is offline
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,448

    Re: Average without counting not used cells?

    or =AVERAGEIF(B3:B8,"<>0",i3:i8) to provide for negative values ?
    How to paste Power Query code ( by Olly)
    Reply With QuoteRegister To Reply
  5. 07-23-2013, 05:29 AM #5
    WolfieReveles
    WolfieReveles is offline
    Registered User
    Join Date
    07-23-2013
    Location
    Solna
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Average without counting not used cells?

    Hi, I posted from Vihral's account yesterday, but I figured I might as well start one of my own

    Thanks a lot for the help, it's nearly working now.
    I tried this: =AVERAGEIF(B3;"<>";F3:H3), repeaded specifically for lines 3 through 8 where F to H cells have a range of 0 to 5 , and a final cell giving the total avarage of the lines that have valid results: =AVERAGEIF(I3:I8;"<6";I3:I8)

    The only issue remaining is that instead of counting the average of, for example, F3-H3, it only counts F3. Any ideas on how to resolve this?


    Thank you!



    -----------------------EDIT-----------------------

    I forgot to mention that if I changed =AVERAGEIF(B3;"<>";F3:H3) for =AVERAGEIF(F3:H3;"<>";F3:H3) the formula works out fine. Can AVERAGEIF only calculate the cells used for the condition?
    Last edited by WolfieReveles; 07-23-2013 at 06:55 AM.
    Reply With QuoteRegister To Reply
  6. 07-23-2013, 06:44 AM #6
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Average without counting not used cells?

    Hi and welcome to the forum.

    Be sure that best way to describe your problem is to upload a sample workbook.

    Be sure that all sensitive data removed, showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that.

    To attach a small sample workbook.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    Reply With QuoteRegister To Reply
  7. 07-23-2013, 07:54 AM #7
    WolfieReveles
    WolfieReveles is offline
    Registered User
    Join Date
    07-23-2013
    Location
    Solna
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Average without counting not used cells?

    Here's an example of what I'm trying to do:
    Example.xlsx
    Reply With QuoteRegister To Reply
  8. 07-23-2013, 08:37 AM #8
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Average without counting not used cells?

    Really i don't understand what are you trying to archive and what is this that don't works for you.

    As you said this formula works fine

    =AVERAGEIF(F3:H3;"<>";F3:H3)

    also this one..

    =IF(B3<>"";AVERAGE(F3:H3);"")

    So??
    Reply With QuoteRegister To Reply
  9. 07-23-2013, 08:46 AM #9
    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 without counting not used cells?

    Perhaps you need this formula in I3 copied down

    =IF(B3="";"";F3)
    Audere est facere
    Reply With QuoteRegister To Reply
  10. 07-23-2013, 09:07 AM #10
    WolfieReveles
    WolfieReveles is offline
    Registered User
    Join Date
    07-23-2013
    Location
    Solna
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Average without counting not used cells?

    Thanks Fotis, =IF(B3<>"";AVERAGE(F3:H3);"") worked great! I guess the error I made was trying to write it as an =AVERAGEIF
    Reply With QuoteRegister To Reply
  11. 07-23-2013, 09:08 AM #11
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Average without counting not used cells?

    You are welcome and thanks for the feed back.

    As that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thank you.
    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)

Similar Threads

  1. [SOLVED] Average column of cells,but ignore errors and return average of numbers that are there...
    By synses in forum Excel General
    Replies: 14
    Last Post: 06-14-2013, 09:00 PM
  2. The rank of average ranks excluding empty cells but including their average.
    By Terminal45 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-21-2012, 03:44 AM
  3. Average without counting zero’s
    By Saturn in forum Excel General
    Replies: 7
    Last Post: 08-24-2011, 05:00 PM
  4. [SOLVED] How do I use an average function, not counting cells containing a
    By Ryder in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-16-2005, 09:06 AM
  5. [SOLVED] Counting down an average
    By Maddoktor in forum Excel General
    Replies: 1
    Last Post: 02-23-2005, 09: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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1