Average in status bar different to formula

[boamorte]

Hi,

I have a strange situation. I want to average the numbers (in time format) in column A only when the indicator "1" appears in column B. At first to eyeball it I filtered column B to 1, selected column A and looked at the status bar which provided the average 00:00:02.

However when I then wanted the data in a table I used the formula:

=AVERAGEIF(B:B,"1",A:A)

This returned 00:00:03. I've played with the filter so I know it does change the average in the status bar when filtered to different values, so why would the average displayed in the status bar be different to the one returned by the averageif formula?

Any help would be greatly appreciated - this second makes a big difference to what I'm doing!

 

[Firefly2012]

Rounding effects maybe? Try changing the formatting to a number format with many decimal places and then recomparing.

 

[boamorte]

Tried this.
Status bar returns 0.000035118...
Formula returns 0.000026587...

Not entirely familiar with exactly how time works in Excel but this still seems weird. Any ideas?

 

[boamorte]

Sorry figures in last post should be the other way round - status bar returns ...26...., formula returns ...35...

 

[Firefly2012]

How many values are we talking here (in the filtered list)?

Do you get the same problem if you physically copy the filtered data to a new sheet (so it is only the filtered data) and then compare the values again?

 

[boamorte]

Almost 15,000 values in filtered list.

Yes same thing occurs when copying to new sheet - status bar show 00:00:03, =average(A:A) shows 00:00:02.

I'm stumped as to why this is happening but I have found a workaround which is simply to add a new column using =60*minute(a1)+second(a1) to express the values in A as a number of seconds. The averages now match and I'll use this data, so I'm fine, but I'm still mystified about the original problem!

 

[Maci3j]

Can it be that some variables were left out when you filtered them?
Try to combine if and average function =if(......;average()). this to avoid filter problems.
Can you give us an example of your table? pic is also good.