![[TEST] Document Foundation Community](https://communitytest.documentfoundation.org/images/discourse-logo-sketch.png){kind=logo}
I have developed a spreadsheet which contains a sheet for the cover and basic information as well as one sheet for every month. There is a cell with a number on every sheet at location H35. I want the average of all of those which are not zero on the first sheet. I initially tried using "AVERAGEIF(January.H35:December.H35, ">0")", but it keeps giving me error 504. I then tried the formula below, which uses COUNTIF, but the part with COUNTIF in it causes a 504 also. If I cannot use COUNTIF or AVERAGEIF, how do I get an average of cell H35 on each sheet where H35 is greater than zero?
One way would be to add a second cell to each sheet, say H36, that has
a simple "IF(H53>0,1,0)", then on the cover sheet your formula could be
something like
"=(January.H35+Fenruary.H35+...)/(January.H36+February.H36+...)". Of
course you would need to type in all the actual cell references instead
of the ellipses. Better yet would be to split that cover cell into
three cells, one for the sum of all H35 cells, one for the sum of all
H36 cells, and one for the averaging of those two numbers. I'm a fan of
having such intermediate calculations in the spreadsheet. Things get
much simpler (and therefore easier to debug and modify) when you
include intermediate steps and don't try to do too much in one cell.
If adding another cell (or cells) is out of the question, it could all
be done in a user function, say in LO Basic. That would require you to
code a user defined function, and I'm not sure how familiar you are
with that. Depending on your point of view, a user defined function
might be either a more or a less elegant solution, and more or less
difficult to change in future.
Maybe someone else knows a way to do it using existing functions, but I
can only think of those two approaches offhand.
Paul
Well I have designed the sheets to be printed at the end of each year, so extra cells would not work. I do know Basic (I started my coding career using Basic on an Atari 400 in 1986) but would much rather use the built-in functions of Calc. Is there a reason that AVERAGEIF and COUNTIF will not work with data on other sheets? I would REALLY prefer for this to just work, rather than coding in more things that I will have to maintain in the future. In other words, is there any possible way to do this in LO? MSO can do this easily, but we switched to LO a few years back at my suggestion to save thousands of dollars and it has worked wonderfully for us, but Calc seems to have a few issues where Excel does not. I will say though, that this is the first issue I have been unable to solve on my own.
Well, I *think* the issue is with using the functions on a range that
is across worksheets. I don't think you can do that, but then again, I
don't think you can do this in MSO either, but you seem to think that
MSO can do this, so my assumption appears to be wrong, and as such
might be wrong for LO too.
You are right that builtin functions would be the nicest solution.
Given that this is for printing, I would normally recommend a user
function because I would assume that the builtin functions cannot do
cross-worksheet ranges. As to extra cells, extra columns can be created
and then hidden, such that they won't print, or extra worksheets that
won't be printed can be used for the intermediate calculations, so this
might get you around the issue.
I will look into this further, but it's bedtime now 
(Actually 5 am
already... *groan*), so I'm going to leave this until tomorrow, and if
none of the brighter minds have solved this by then, I'll have another
attempt.
Paul
PS. Are you *sure* MSO can do cross worksheet ranges in functions like
this?
I have developed a spreadsheet which contains a sheet for the cover and basic information as well as one sheet for every month. There is a cell with a number on every sheet at location H35. I want the average of all of those which are not zero on the first sheet. I initially tried using "AVERAGEIF(January.H35:December.H35, ">0")", but it keeps giving me error 504.
This sounds a bug: AVERAGE() works on a cross-sheet range, so I don't see why AVERAGEIF() shouldn't.
I then tried the formula below, ...
Er, I don't see a formula below ...
... which uses COUNTIF, but the part with COUNTIF in it causes a 504 also.
Isn't that also a bug?
One way would be to add a second cell to each sheet, say H36, that has a simple "IF(H53>0,1,0)", then on the cover sheet your formula could be something like
=(January.H35+February.H35+...)/(January.H36+February.H36+...)
Of course you would need to type in all the actual cell references instead of the ellipses.
Since SUM() *does* work on cross-sheet ranges, you could simplify this to
=SUM(January.H35:December.H35)/SUM(January.H36:December.H36)
Maybe someone else knows a way to do it using existing functions, ...
Keep watching.
Well I have designed the sheets to be printed at the end of each year, so extra cells would not work.
That is no problem: the intermediate cells could be on other sheets or simply outside the print range. Another way of using intermediate values would be simply to have a range of twelve cells on your first sheet that simply contain =January.H35 and so on. If preferred, this range could be outside your print range - or even hidden. AVERAGEIF() should then work straightforwardly on this range of copies.
Is there a reason that AVERAGEIF and COUNTIF will not work with data on other sheets?
Not that I can see.
... is there any possible way to do this in LO?
This is a messy workaround, but it appears to work:
=SUM(January.H35:December.H35)/(COUNT(January.H35:December.H35)-FREQUENCY(January.H35:December.H35;0))
I trust this helps.
Brian Barker
It certainly sounds like a bug however that being said, but perhaps your AVERAGEIF formula needs work.
Unfortunately my older version of LO does not have AVERAGEIF so I cannot check the syntax. Perhaps <http://www.libreoffice.org/get-help/documentation/> will have the answer for you.
Post the answer here or the bug number on this thread.
Hope you come right.
Hylton
How about bringing the data from the other sheets to the first sheet, or to a 'work' sheet and calcing the data on that sheet. When you print, just don't print the work sheet.
John
Hi,
Paul schrieb:
Well, I *think* the issue is with using the functions on a range that
is across worksheets. I don't think you can do that,
But the specification allows a cuboid reference. Therefore it is a bug, or at least a missing feature. For AOO the issue is https://issues.apache.org/ooo/show_bug.cgi?id=110421
but then again, I
don't think you can do this in MSO either, but you seem to think that
MSO can do this, so my assumption appears to be wrong, and as such
might be wrong for LO too.
Excel 2010 does not allow this kind of reference for its xxxIF functions. For other functions like SUM such cuboid reference works.
Kind regards
Regina
Exactly. All of the functions that I use seem to work across sheets, except AVERAGEIF and COUNTIF. I will look into filing a bug report. For now, is there any way to get the average of cell H35 across all of my sheets without adding other sheets or modifying my layout? I will have people using this form who struggle to understand what the start button is, and saying "print all except the last sheet" will blow their minds. Thanks for all of the help thus far. Oh, and the formula I tried when I found that AVERAGEIF would not work is below. It works except for COUNTIF.
=SUM(January.H35:December.H35)/(COUNT(January.H35:December.H35)-COUNTIF(January.H35:December.H35,"=0"))
-Ryan
Yes. Did you not read my solution - that you quoted in your message?!
Brian Barker
I read your solution, but do not want to alter any cells or add new ones as the spreadsheets are formatted to fit onto a single sheet of paper. I am going to test the solution using frequency right now. It looks perfect to me, but I won't know until I put some data into the sheets and see what happens. I will report back soon. Thank you all for your suggestions and feedback!
-Ryan
This has been a most enlightening discussion.
I am a big fan of open source software. That said, this discussion has shined a bright light on one of the most problematic areas of the concept.
I might be totally off base here but as I view it, without a central management structure to _enforce_ standards two separate groups of developers may develop what appear to the end user as being similar related features that are inconsistent with each other. This results in unpleasant surprises for the end user.
In the instance discussed in this thread, functions such as SUM(), COUNT(), AVERAGE(), and probably FREQUENCY() have been developed with support for three dimensional (3D) ranges of cells (sheet, row, and column) while functions such as SUMIF(), COUNTIF(), and AVERAGEIF() were developed with support for only the more traditional two dimensional (2D) ranges of cells (row and column).
I have no idea how difficult it would be to implement the 3D paradigm for the xxxIF() functions but I know that it's exactly what I've wanted several times in the past without any concept as to the syntax that could be used that would make the functionality possible.
In this case the 3D paradigm can be fully implemented without breaking function calls already in .xsl documents even if Excel hasn't implemented the 3D paradigm so I hope it will be done in LO.
Are developers reading what's posted here?
I read your solution, but do not want to alter any cells or add new
ones
as the spreadsheets are formatted to fit onto a single sheet of paper.
I
am going to test the solution using frequency right now. It looks
perfect to me, but I won't know until I put some data into the sheets
and see what happens. I will report back soon. Thank you all for your
suggestions and feedback!-Ryan
This is a messy workaround, but it appears to work:
=SUM(January.H35:December.H35)/(COUNT(January.H35:December.H35)-FREQUENCY(January.H35:December.H35;0))
Nothing super messy about it though the 3D paradigm applied to AVERAGEIF() would indeed be cleaner.
Peter West
"Where is he who has been born king of the Jews?"
This has been a most enlightening discussion.
I am a big fan of open source software. That said, this discussion has shined a bright light on one of the most problematic areas of the concept.
I might be totally off base here but as I view it, without a central management structure to _enforce_ standards two separate groups of developers may develop what appear to the end user as being similar related features that are inconsistent with each other. This results in unpleasant surprises for the end user.
If there had been a central management structure to _enforce_ standards, there would be NO open source software, full stop. No linux, no OS X, no samba, no python, no MySql, no Open Office and no Libre Office, to mention but a few.
The productivity of OSS development teams, imperfect as it is, cannot be matched by any structures designed for the management of software development. It is only matched by startups with small, committed teams and inspired technical leadership. Success and rapid increases in the scale of the development teams kills their productivity. Companies that have carved out a fantastically profitable niche can survive this transition, bit they can only retain a fraction of their original productivity.
Hi
You guys are over-complicating it. Just post a "feature request" via
the bug-reporting system
Similar problems occur in proprietary systems too but with less chance
of reporting the problem. Also the bosses need to understand the
problem and give it more attention than their golf handicap before
bothering to do anything about it. If it doesn't affect profits then
why spend money on fixing it? OpenSource allows anyone to get on with
sorting out a fix without having to wait for the bosses to give the
orders.
Also although the organisational structure is largely non-hierarchical
there are still ISO standards and other external rules to guide
development, there are committees, working groups, team discussions
and informal chats. At worst, development becomes more Darwinian with
successful forks developing and taking the lead positions but getting
inspired by other projects and even copying chunks of code from them.
Look at how Ubuntu was in the number 1 slot at Distrowatch for years
but now Mint has taken over. Mandrake evolved into Mandriva but then
forked into Mageia and now Mageia is far higher than either of it's
forebears managed.
Regards from
Tom
I wanted to reply and let everybody know that the workaround using FREQUENCY works fine. The sheet is now out for users to test and will be going through a few cosmetic changes, but it works. If you are curious, the sheet is a vehicle mileage sheet for company vehicles, but I will be using it for my personal truck as well. It has all kinds of useful information such as monthly average mpg/fuel cost/miles driven, as well as the minimum and maximum of those variables. The cover sheet has averages for the entire year. Simple to use and very useful for our crew.
Thanks again for all of the help! I will file a feature request when I get time. Being the lead IT and IS guy in a small business keeps me busy...
i will suggest SUM and COUNTA, but this last function does not actualize inmediately its value when its source data change, if the change hapens in any sheet not containing COUNTA. The solution is save and reopen the worksheet.