+ Reply to Thread
Results 1 to 10 of 10

Excel 2007 : Averaging #'s only when data is placed / Not counting blank cells

  1. #1
    Registered User
    Join Date
    12-21-2008
    Location
    Dupont PA
    Posts
    99

    Averaging #'s only when data is placed / Not counting blank cells

    I thought I had the correct formulas in but as soon as data was being placed week by week..I noticed the flaws.

    I have D5 that will need to calculate the overall average monthly gas usage by my employer. The key is not to include blank cells into the average.

    The second part to this is the individual averages (D8:32) also not including zeros/blank cells. I can't just divide by 52 because sometimes gas will not be purchased. So if a week doesn't have data in the cell, ($0.00) can I do a sum if equation to only average the cells with data accordingly?


    I apologize about the size of the file...I dont know how to just save an individual sheet and send only that.
    Attached Files Attached Files
    Last edited by berk21; 03-05-2009 at 09:55 PM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Averaging #'s only when data is placed / Not counting blank cells

    Given you're running your 2007 you could think about using the AVERAGEIF and AVERAGEIFS functions ... note that these functions are not backwards compatible with earlier versions.

    D8:
    =IF(COUNTIF($E8:$BP8,">0"),AVERAGEIFS($E8:$BP8,$E$7:$BP$7,"<>Totals",$E8:$BP8,"<>0"),0)
    copy down to D32

    Have a go at resolving the other using these functions - check out XL Help for more info.

    If you can't use AVERAGEIFS (because you may run on earlier versions) then you would need to use an Array function

    D8:
    =IF(COUNTIF($E8:$BP8,">0"),AVERAGE(IF(($E$7:$BP$7<>"Totals")*($E8:$BP8),$E8:$BP8)),0)
    confirmed with CTRL + SHIFT + ENTER
    copy down to D32

    Note the above assume only +ve values (ie no credits) -- if that's not the case let us know... to do with validating as to whether or not to conduct the AVERAGE calc in the first instance... using COUNTIF <> 0 is risky given if text were entered by accident a #VALUE! error would be retuned... can combine COUNT and COUNTIF but may be overkill in this instance.
    Last edited by DonkeyOte; 03-03-2009 at 05:11 AM.

  3. #3
    Registered User
    Join Date
    12-21-2008
    Location
    Dupont PA
    Posts
    99

    Re: Averaging #'s only when data is placed / Not counting blank cells

    I will insert this to my sheet and let you know if it works. Will the same concept work for cell D4?

  4. #4
    Registered User
    Join Date
    12-21-2008
    Location
    Dupont PA
    Posts
    99

    Re: Averaging #'s only when data is placed / Not counting blank cells

    not looking right. If you enter values into any TWO weeks (just to keep it simple) the average is not correct. For instance, I placed $111.00 into week one of Janurary and $111.00 into week two. $222.00 divided by 2 doesn't = $148.00.

    That means he/she averages more than they spend in a single week??
    Mathematically impossible.

    Correct me if I am wrong. But here is the one I tested back with values in place.
    Attached Files Attached Files

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Averaging #'s only when data is placed / Not counting blank cells

    The "Totals" headers are in Row 6 rather than 7:

    D8:
    =IF(COUNTIF($E8:$BP8,">0"),AVERAGEIFS($E8:$BP8,$E$6:$BP$6,"<>Totals",$E8:$BP8,"<>0"),0)

    Presently the Totals are included in the Average... (111+111+222)/3 = 148

  6. #6
    Registered User
    Join Date
    12-21-2008
    Location
    Dupont PA
    Posts
    99

    Re: Averaging #'s only when data is placed / Not counting blank cells

    Looks good now...as far as the technicians are concerned.

    For cell D5 what range and criteria is compared? Just can't grasp how you got this.


    =IF(COUNTIF($E8:$BP8,">0"),AVERAGEIFS($E8:$BP8,$E$6:$BP$6,"<>Totals",$E8:$BP8,"<>0"),0)

    $E$6:$BP$6 are the months...where does that matter? anyway...you dont have to go into a full blown out explanation. I understand you help, not teach.

    Novice when it comes to the advanced formulas.
    Attached Files Attached Files

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Averaging #'s only when data is placed / Not counting blank cells

    Perhaps:

    =IF(COUNTIF($E33:$BP33,">0"),AVERAGEIFS($E33:$BP33,$E$6:$BP$6,"<>Totals",$E33:$BP33,"<>0"),0)

    Note:

    Your Monthly total values are in H onwards not I onwards... with Merged cells the value is always in the leftmost/top cell of the merged area.

    I would suggest you avoid using Merged Cells altogether especially if dealing with horizontal merges you are far better off using "Centre Across Selection" feature which does the same thing without the resulting headaches. A lot of us would like to see the Merged Cells feature removed from XL altogether.

  8. #8
    Registered User
    Join Date
    12-21-2008
    Location
    Dupont PA
    Posts
    99

    Re: Averaging #'s only when data is placed / Not counting blank cells

    =IF(COUNTIF($E34:$BP34,">0"),AVERAGEIFS($E34:$BP34,$E$6:$BP$6,"<>Totals",$E34:$BP34,"<>0"),0)

  9. #9
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Averaging #'s only when data is placed / Not counting blank cells

    I'm not altogether sure I quite understand the relevance/significance of your last post, based on your sample file the formula I provided used the correct ranges... if your *real* range is different that's fine but it bears no relevance to the files you've uploaded historically.

  10. #10
    Registered User
    Join Date
    12-21-2008
    Location
    Dupont PA
    Posts
    99

    Re: Averaging #'s only when data is placed / Not counting blank cells

    I dont know what I thought....for some reason I was using yours and it didnt work...so I changed it and posted it...now that I am looking at it...makes no sense either....I must have thought I corrected something but really didnt ....so my fault and thanks for all your help on this.

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1