Aggregate Function Not Working Properly

[melvins138]

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

[mariosv]

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:

USING LOOKUPs but not working Libreoffice

=IF(OR(F16=0;F16="");"";(G16-IF(OR(G15=0;G15="");LOOKUP(2;1/((G$3:G14<>"")(G$3:G14<>0));G$3:G14);G15))/(F16-IF(OR(F15=0;F15="");LOOKUP(2;1/((F$3:F14<>"")(F$3:F14<>0));F$3:F14);F15))*100) It works in MS excel but not in Libreoffice. LOOKUP(2;1/ formula doesn’t work in Libreoffice How should I write it in Libreoffice that will work?

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.