averageifs randomly returning #DIV/0! when calculating minute averages

[bgallagher1]

I am trying to calculate minute averages for data entered every five seconds, and I am getting seemingly random #DIV/0! errors.

The format of the data is:

Row 1 contains column headings
Columns A-H contain data points
Column I contains a time stamp
Column J contains the time stamp rounded down to the nearest minute
Column J is a "helper column" containing a count for each minute

I have tried using the averageifs function using both the rounded down time and the minute count columns as the criteria. This works for the most part, but I get #DIV/O! intermittently and I can't for the life of me figure out why!

Also odd is these #DIV/0! errors for the most part don't match up between the two calculation methods for any given minute average!!

I've uploaded an example spreadsheet to dropbox here https://dl.dropboxusercontent.com/u/64847883/example/example.xlsx

Any help you can offer would be much appreciated!

 

[Aladin Akyurek]

If the condition does not occur, you can get #DIV/0!.

=IF(COUNTIFS(ConditionRange,Condition),AVERAGEIFS(AvgRange,ConditionRange,Condition),"")

would suppress this error.

 

[Kevin Neufeld]

in cell N4

=IFERROR(AVERAGEIFS(A:A,$K:$K,$M4),0)

 

[chicagocomputerclasses]

Numbers in column K are rounded up with formatting, it's not really a whole number in the cell and it doesn't match the condition. Therefore you get DIV/0 error.

 

[bgallagher1]

Thanks for replies all.

I understand that #DIV/O! displays when the condition doesn't occur -- I guess my problem is I don't understand why the condition wouldn't be occurring ie why it would calculate averages for for 9:52:00 and 9:54:00 just fine but throw an error for 9:53:00.

Thanks for the catch on the helper column not being whole numbers -- I updated the sheet linked before accordingly and still getting the same errors. Seems calculating using the actual time rather than the helper column throws a lot less errors -- but I still don't understand why.

 

[chicagocomputerclasses]

You still have the same problem. Change the formulas in column K to =ROUNDUP((J4-$J$3)*1440,0) and you should be fine.

 

[bgallagher1]

You still have the same problem. Change the formulas in column K to =ROUNDUP((J4-$J$3)*1440,0) and you should be fine.

that did the trick, thank you!

any idea for the averages calculated via the time rounded down?

 

[chicagocomputerclasses]

Just use ROUNDDOWN function. Make sure you understand how these functions work.

ROUNDUP

[FONT=&quot]=ROUNDUP(3.2,0)[/FONT]	[FONT=&quot]Rounds 3.2 up to zero decimal places.[/FONT]	[FONT=&quot]4[/FONT]
[FONT=&quot]=ROUNDUP(76.9,0)[/FONT]	[FONT=&quot]Rounds 76.9 up to zero decimal places.[/FONT]	[FONT=&quot]77[/FONT]
[FONT=&quot]=ROUNDUP(3.14159, 3)[/FONT]	[FONT=&quot]Rounds 3.14159 up to three decimal places.[/FONT]	[FONT=&quot]3.142[/FONT]
[FONT=&quot]=ROUNDUP(-3.14159, 1)[/FONT]	[FONT=&quot]Rounds -3.14159 up to one decimal place.[/FONT]	[FONT=&quot]-3.2[/FONT]
[FONT=&quot]=ROUNDUP(31415.92654, -2)[/FONT]	[FONT=&quot]Rounds 31415.92654 up to 2 decimal places to the left of the decimal point.[/FONT]	[FONT=&quot]31500[/FONT]

<tbody style="box-sizing: border-box;">
</tbody>

ROUNDDOWN

[FONT=&quot]=ROUNDDOWN(3.2, 0)[/FONT]	[FONT=&quot]Rounds 3.2 down to zero decimal places.[/FONT]	[FONT=&quot]3[/FONT]
[FONT=&quot]=ROUNDDOWN(76.9,0)[/FONT]	[FONT=&quot]Rounds 76.9 down to zero decimal places.[/FONT]	[FONT=&quot]76[/FONT]
[FONT=&quot]=ROUNDDOWN(3.14159, 3)[/FONT]	[FONT=&quot]Rounds 3.14159 down to three decimal places.[/FONT]	[FONT=&quot]3.141[/FONT]
[FONT=&quot]=ROUNDDOWN(-3.14159, 1)[/FONT]	[FONT=&quot]Rounds -3.14159 down to one decimal place.[/FONT]	[FONT=&quot]-3.1[/FONT]
[FONT=&quot]=ROUNDDOWN(31415.92654, -2)[/FONT]	[FONT=&quot]Rounds 31415.92654 down to 2 decimal places to the left of the decimal point.[/FONT]	[FONT=&quot]31400[/FONT]

<tbody style="box-sizing: border-box;">
</tbody>