Bug 137763 - Aggregate(1;6;..) (Avg): different conditions => same value or single values and not the average
Summary: Aggregate(1;6;..) (Avg): different conditions => same value or single values ...
Status: RESOLVED WORKSFORME
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
6.4.6.2 release
Hardware: x86 (IA32) Windows (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2020-10-26 09:55 UTC by gooly
Modified: 2020-11-30 14:18 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
calc file with data and Aggregate (32.24 KB, application/vnd.oasis.opendocument.spreadsheet)
2020-10-26 09:57 UTC, gooly
Details

Note You need to log in before you can comment on or make changes to this bug.
Description gooly 2020-10-26 09:55:19 UTC
Description:
Enclosed is the file for the use of Aggregate.
The relevant columns are E, F ( G = combination of E & F) and the result column T.
Black numbers are correct, red numbers are wrong. Either other conditions lead to equal mean values (1253) - cannot be!
Or single values (T12,T13) of the result column are displayed Z12,Z13 - cannot be either.
The solution, which shows the error division by zero (#DIV/0!) Z11 is a proposed solution from the German forum.

I want the average of those values of the result column T for which En=Fn applies, i.e. E="Se" && F="Se" || E="Bu" && F="Bu", therefore in Y11:
=AGGREGAT(1;6;T11:T84;(E11:E84=F11:F84)).
The rest is to test Aggregate() to understand it.

Steps to Reproduce:
1. Just look at the cells with Aggregate as described above
2. The link to download the calc file: https://we.tl/t-74RdgENvdj
3.

Actual Results:
Wrong values of AGGREGATE

Expected Results:
Correct values


Reproducible: Always


User Profile Reset: Yes


OpenGL enabled: Yes

Additional Info:
Calc. correctly.
Comment 1 gooly 2020-10-26 09:57:17 UTC
Created attachment 166728 [details]
calc file with data and Aggregate
Comment 2 Mike Kaganski 2020-10-26 10:17:39 UTC
Reading the description and looking at attachment 166728 [details], I don't quite see what should I inspect for the error.

The description tells:

> The relevant columns are E, F ( G = combination of E & F) and the result column T.
> Black numbers are correct, red numbers are wrong.

But it doesn't mention which sheet (of the two in the attachment) is meant. Likely "Ma03", because the other one doesn't contain the data there? but then the columns mentioned in the description don't contain the AGGREGATE formulas? (Only G has a formula at all...)

Is that column Z (specifically, cells Z11:Z13) that one needs to inspect?
Comment 3 Mike Kaganski 2020-10-26 10:21:14 UTC
An unrelated note: you may optimize the formula in G11 to be '=IF(E11=F11;E11;E11&F11)', if you only expect "Se" and "Bu" there, or if you are OK with simple concatenation of the different cell values.
Comment 4 gooly 2020-10-26 10:25:15 UTC
1. Sorry it is of course only the first table not the second (small) one.
2a. Just look at the red values (same) and then at the parameters of Aggregate (different conditions). I expect different averages for different conditions.
2b. How come that single values are taken and obvioulsy not an average?
Comment 5 gooly 2020-10-26 10:26:31 UTC
Yes Z is the column from which the average has to be calculated.
Comment 6 Mike Kaganski 2020-10-26 10:27:01 UTC
Actually, pressing F9 in those Z11:Z13 makes all three show the same value. Is this a duplicate of tdf#137248?
Comment 7 gooly 2020-10-26 13:17:22 UTC
No as I never pressed F9. These values are calculated as soon as I press enter after writing the formula in the cell.
Comment 8 Mike Kaganski 2020-10-26 13:44:31 UTC
(In reply to gooly from comment #7)

I am not sure if I understand your reply.
Anyway, testing with Version: 7.0.3.1 (x64)
Build ID: d7547858d014d4cf69878db179d326fc3483e082
CPU threads: 12; OS: Windows 10.0 Build 19041; UI render: Skia/Raster; VCL: win
Locale: ru-RU (ru_RU); UI: en-US
Calc: CL,

and pressing F9 in following cells, I get what looks *correct*:

Z12: 1558,83 (the same as in Z11)
Z13: 1558,83 (the same as in Z11)
Y11: 635,55
X11: 635,51

So the question is: are these values what you expect? If yes, then you should update your LibreOffice.
Comment 9 gooly 2020-10-27 09:26:37 UTC
Well after upgrading to 7.0 the error disappears, so you might close it.
Comment 10 Mike Kaganski 2020-10-27 09:35:02 UTC
(In reply to gooly from comment #9)
Nice!

Now, after the bug had been resolved, here is my advise for your original task:

if you need to get average for some values, you may use this in T12:

  =AVERAGEIF(G11:G84;"Bu|Se";T11:T84)

... if you use regular expressions in your spreadsheet (see Options->Calc->Calculate). This is much simpler (and more manageable) than

  =AGGREGATE(1;6;T11:T84/((E11:E84="Se")*(F11:F84="Se")+(E11:E84="Bu")*(F11:F84="Bu")))
Comment 11 gooly 2020-10-27 10:00:52 UTC
This =AVERAGEIF(G11:G84;"Bu|Se";T11:T84)
(in German: =MITTELWERTWENN(G11:G84;"Bu|Se";T11:T84) )
causes "#DIV/0!"
LO 7.0.3.1 (64)
Comment 12 Mike Kaganski 2020-10-27 10:04:44 UTC
(In reply to gooly from comment #11)
> This =AVERAGEIF(G11:G84;"Bu|Se";T11:T84)
> (in German: =MITTELWERTWENN(G11:G84;"Bu|Se";T11:T84) )
> causes "#DIV/0!"
> LO 7.0.3.1 (64)

... which means that you didn't allow regular expressions, as I mentioned in comment 10.
Comment 13 gooly 2020-10-27 10:57:26 UTC
OK, I had to learn that I have to allow it generally and not (only) for the required function - now it works :)
Comment 14 Xisco Faulí 2020-11-30 14:18:37 UTC
Setting to RESOLVED WORKSFORME since the commit fixing this issue hasn't been
identified.