Date: prev next · Thread: first prev next last
2014 Archives by date, by thread · List index

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


PS. Are you *sure* MSO can do cross worksheet ranges in functions like

On Sat, 04 Jan 2014 21:38:23 -0500
Ryan Ashley <> wrote:

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.

On 1/4/2014 8:28 PM, Paul wrote:
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.


On Sat, 04 Jan 2014 19:10:11 -0500
Ryan Ashley wrote:

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?

To unsubscribe e-mail to:
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot be deleted


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.