+ Reply to Thread
Results 1 to 13 of 13

Formula is counting blank cells in the range, causing averaging to give incorrect result

  1. #1
    Registered User
    Join Date
    09-19-2014
    Location
    NC
    MS-Off Ver
    2007
    Posts
    7

    Formula is counting blank cells in the range, causing averaging to give incorrect result

    Hello and thanks in advance for your assistance.

    I have a simple spreadsheet with data values that is updated daily. I am trying to get the following formula to properly calculate the average of "PUE" when the month is ongoing, hence blanks.

    For September "SEP" the average is counting 30 days, causing the average for the month to calculate using 11 blank rows. The result should be 2.3.

    Rick



    =SUMIF('Data Daily'!A2:A366,"NO",'Data Daily'!C2:C366)/((COUNTIF('Data Daily'!A2:A366,"SEP")-(COUNTBLANK(A2:A366)="")))

    Month PUE
    Apr-14 2.4 2.4
    May-14 2.3 2.3
    Jun-14 2.3 2.3
    Jul-14 2.2 2.2
    Aug-14 2.3 2.3
    Sep-14 2.3 1.4
    Attached Images Attached Images

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,736

    Re: Formula is counting blank cells in the range, causing averaging to give incorrect resu

    We need a workbook
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    09-19-2014
    Location
    NC
    MS-Off Ver
    2007
    Posts
    7

    Re: Formula is counting blank cells in the range, causing averaging to give incorrect resu

    Thanks - Workbook uploaded
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    09-17-2014
    Location
    New York
    MS-Off Ver
    2013
    Posts
    20

    Re: Formula is counting blank cells in the range, causing averaging to give incorrect resu

    average help.xlsmput this in before your formula =if(a1=0,"", (the rest of your formula but take out the equals sign). if it is not a formula just delete the 0 you are inputting into the cell.
    The other option is to have a hidden row that only references your cells in the formula and will allow you to use the formula. This way you can use the hidden cells for the average calculation, but your could the blanks would still work.

  5. #5
    Registered User
    Join Date
    09-19-2014
    Location
    NC
    MS-Off Ver
    2007
    Posts
    7

    Re: Formula is counting blank cells in the range, causing averaging to give incorrect resu

    I apologize, but that doesn't make sense to me.

    The formula does not contain any equals sign...........

    =SUMIF('Data Daily'!A2:A366,"SEP",'Data Daily'!C2:C366)/((COUNTIF('Data Daily'!A2:A366,"SEP")-(COUNTBLANK('Data Daily'!A2:A366)="SEP")))

    Quote Originally Posted by New2vbabutloveit View Post
    Attachment 346544put this in before your formula =if(a1=0,"", (the rest of your formula but take out the equals sign). if it is not a formula just delete the 0 you are inputting into the cell.
    The other option is to have a hidden row that only references your cells in the formula and will allow you to use the formula. This way you can use the hidden cells for the average calculation, but your could the blanks would still work.

  6. #6
    Registered User
    Join Date
    09-17-2014
    Location
    New York
    MS-Off Ver
    2013
    Posts
    20

    Re: Formula is counting blank cells in the range, causing averaging to give incorrect resu

    lol your formula appears to be working perfectly fine

    when I selected the data for April it says the average of the april data is 2.39312488

    and your calculation is accurate to 9 places.

    if you are looking to get rid of the cells showing #div/0! the input in front of your formula :
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    09-19-2014
    Location
    NC
    MS-Off Ver
    2007
    Posts
    7

    Re: Formula is counting blank cells in the range, causing averaging to give incorrect resu

    It appears the the following is not correctly counting how many rows are blank for the Range in A2:A366 for "SEP"

    -(COUNTBLANK('Data Daily'!A2:A366)="SEP")))

  8. #8
    Registered User
    Join Date
    09-17-2014
    Location
    New York
    MS-Off Ver
    2013
    Posts
    20

    Re: Formula is counting blank cells in the range, causing averaging to give incorrect resu

    =SUMIF('Data Daily'!A2:A366,"SEP",'Data Daily'!C2:C366)/((COUNTIF('Data Daily'!A2:A366,"SEP")-(COUNTBLANK('Data Daily'!A2:A366)="SEP")))
    I call it a equal sign, but that is what I was referring to.

  9. #9
    Registered User
    Join Date
    09-19-2014
    Location
    NC
    MS-Off Ver
    2007
    Posts
    7

    Re: Formula is counting blank cells in the range, causing averaging to give incorrect resu

    I really do appreciate the help, but look at Cell C9.

    I was using that as a test cell even though A9 says NOV, I am using that cell to test the formula for SEP.

    The Total for SEP in the "Data Daily" tab is 41.4. If averaged excluding Rows 263 - 274, the average is 2.3. If [as the current formula is] you divide by 30 days [which includes 12 blank cells or days, which the -COUNTBLANK portion of the argument should be counting], the average drops to 1.4

  10. #10
    Registered User
    Join Date
    09-17-2014
    Location
    New York
    MS-Off Ver
    2013
    Posts
    20

    Re: Formula is counting blank cells in the range, causing averaging to give incorrect resu

    Again calculating the average of the PUE even when there are blanks is working perfectly, again out to 9 places.
    under your format showing numbers click the

    <-0
    00
    to show you the decimal places and the one to the right of it to remove.

  11. #11
    Registered User
    Join Date
    09-17-2014
    Location
    New York
    MS-Off Ver
    2013
    Posts
    20

    Re: Formula is counting blank cells in the range, causing averaging to give incorrect resu

    ok I see, the formula you are using in b7 for sept is perfect. use that and on your 'Data Daily' tab don't use "0" in the data field or it will use it in the average. So you have to get rid of them.

    =AVERAGEIFS('Data Daily'!$C$2:$C$366,'Data Daily'!$A$2:$A$366,"SEP") this is the correct formula for the calculation of the averages of the PUE for the month of September. You do not need to count the blank cells (and since they are showing "0" there would never be any blanks).

  12. #12
    Registered User
    Join Date
    09-19-2014
    Location
    NC
    MS-Off Ver
    2007
    Posts
    7

    Re: Formula is counting blank cells in the range, causing averaging to give incorrect resu

    FYI

    The formula needed to be altered as follows:

    =SUMIF('Data Daily'!A2:A366,"SEP",'Data Daily'!C2:C366)/((COUNTIF('Data Daily'!A2:A366,"SEP")-COUNTIFS('Data Daily'!A2:A366,"sep",'Data Daily'!C2:C366,

    Using the COUNTIFS function rather than the COUNTBLANK function

  13. #13
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Thumbs up Re: Formula is counting blank cells in the range, causing averaging to give incorrect resu

    Try this..
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Check the attached file..
    Attached Files Attached Files
    Regards,
    Vikas Gautam
    Excel-buzz.blogspot.com

    Excel is not a matter of Experience, its a matter of Application.

    Say Thanks, Click * Add Reputation

+ 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. [SOLVED] Formula to give yes or no result based on cells data
    By twiztid in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-06-2014, 05:33 AM
  2. [SOLVED] Age range formula counting blank cells
    By te31 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-05-2013, 02:23 PM
  3. [SOLVED] using a max formula on a column with no data to give a Blank result
    By Brentsa in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-02-2013, 05:42 AM
  4. Replies: 5
    Last Post: 08-26-2013, 06:26 PM
  5. Replies: 9
    Last Post: 03-05-2009, 09:55 PM

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