Ask Your Question
0

Aggregate Function Not Working Properly

asked 2017-01-20 20:26:38 +0200

melvins138 gravatar image

updated 2017-01-20 20:28:28 +0200

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

Can someone please help me?

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

Thanks In Advance, Melvins138

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2017-01-21 00:08:43 +0200

m.a.riosv gravatar image

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-01-20 20:26:38 +0200

Seen: 56 times

Last updated: Jan 21 '17