# Aggregate Function Not Working Properly

I have a formula that works in Excel, and I thought I had it working in LibreOffice, but I am getting different results between programs. I am dealing with a forum database tens of thousands rows long, so I can't do it manually like I have it here.

I have a table that should look like this (and does in Excel):

messageid   threadid    replyto Last messageid in thread
3550        3550        0       3550
3867        3867        0       4677
3947        3867        3867
3998        3867        3947
4337        3867        3998
4414        3867        4337
4481        3867        4414
4677        3867        3867
3925        3925        0         7200
4653        3925        3925
4959        3925        4653
7200        3925        3925
4092        4092        0         4092
5800        5800        0         5800
5802        5802        0         5802
7202        7202        0         7202
7203        7203        0         7656
7294        7203        7203
7305        7203        7203
7342        7203        7203
7406        7203        7342
7436        7203        7305
7656        7203        7203


Quick Explanation, I am trying to find the last 'messageid' within a threadid. If the 'replyto' is 0, then I get a result, if it's <> 0, then blank.

I used this function in Excel with success

=IF(C2=0,AGGREGATE(14,6,$A$2:$A$24/($B$2:$B$24=B2),1),"")


In LibreOffice, I get different results using the same function. Instead of the last (largest) number from Col. A showing, I get the first number, so it looks like this:

messageid   threadid    replyto Last messageid in thread
3550        3550        0       3550
3867        3867        0       3867
3947        3867        3867
3998        3867        3947
4337        3867        3998
4414        3867        4337
4481        3867        4414
4677        3867        3867
3925        3925        0        3925
4653        3925        3925
4959        3925        4653
7200        3925        3925
4092        4092        0        4092
5800        5800        0        5800
5802        5802        0        5802
7202        7202        0        7202
7203        7203        0        7203
7294        7203        7203
7305        7203        7203
7342        7203        7203
7406        7203        7342
7436        7203        7305
7656        7203        7203


I don't have access to Excel at work, and I use LibreOffice, which I normally enjoy and have no issues with.

edit retag close merge delete

Sort by » oldest newest most voted

I think the issue is that you are using the formula in a way that it's not available with calc.

=IF(C2=0,AGGREGATE(14,6,$A$2:$A$24/($B$2:$B$24=B2),1),"")

Excel doesn't take in account error values on $A$2:$A$24/($B$2:$B$24=B2), by zero divisions, while calc do so.

A similar issua than in: https://ask.libreoffice.org/en/questi...

But I think a formula like:
=MAXIFS($A$2:$A$24;$B$2:$B$24;$B2) or =SUMPRODUCT(MAX($A$2:$A$24*($B$2:$B$24=$B2)))
does the job.

more