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

Thanks - Workbook uploaded

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.

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")))

Originally Posted by New2vbabutloveit

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.

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:

Please Login or Register  to view this content.

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")))

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

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

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.

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

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