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.
Created attachment 166728 [details] calc file with data and Aggregate
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?
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.
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?
Yes Z is the column from which the average has to be calculated.
Actually, pressing F9 in those Z11:Z13 makes all three show the same value. Is this a duplicate of tdf#137248?
No as I never pressed F9. These values are calculated as soon as I press enter after writing the formula in the cell.
(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.
Well after upgrading to 7.0 the error disappears, so you might close it.
(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")))
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)
(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.
OK, I had to learn that I have to allow it generally and not (only) for the required function - now it works :)
Setting to RESOLVED WORKSFORME since the commit fixing this issue hasn't been identified.