AVERAGEIFS function help

[Maxito]

Hello!
I’m stuck trying to use the AVERAGEIFS function to reduce a large table with measurement values for almost each second for the duration of one day. The intention is to reduce this by averaging measurements for each minute.

Table 1 is arranged like:

date | time | value

dd:mm:yy | hh:mm:ss | xx

For a table with average values for each minute I created a separate spreadsheet with only two columns

time | average(value)

hh:mm | xx

The function at the second table for the average looks like this:

=AVERAGEIFS($Tabelle1.$C$2:$C$42598,$Table1.$B$2:$B$42598,">="&$A2,$Table1.$B$2:$B$42598,"<"&$A3)

…resulting in a #DIV/0! error. Where did I go wrong?

[Maxito]

Hmm…I think I found the error. Strangely it seems to be purely a formatting error which I do not get resolved yet. I do have another document where exactly the same function does work. But it stops working with the current document due to the formatting of the time column. Which is strange since it is set to the same time and HH:MM:SS format code. The only difference is that the other document displays the exact same formatting with entries being aligned right whereas this one aligns left.

[mark_t]

If the time is aligned right it has probably been recognised as a time and held as a time format that can be used in calculations. If its aligned left then it has probably been entered as a string format which would be treated as zero in calculations.

When you have a string formatted cell and you change the cell to a time format it may still be treated as a string unless you edit the cell content. You might see ’ at the beginning of the string in the formula edit box to indicate the time is still treated as a string.

Try using “View”, “Value Highlighting”. Values in blue can be used in numerical calculations, content in black would return a value of zero.

If you still have a problem try to attach an example to your question, I upvoted your question so you should have enough karma points to add attachment now. Remove any personal information before you upload any files.