Re: [libreoffice-users] Calc cross-sheet issue with COUNTIF and AVERAGEIF...

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?



On Sat, 04 Jan 2014 21:38:23 -0500
Ryan Ashley <ryana@reachtechfp.com> 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.
> >
> > Paul
> >
> >
> >
> > 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: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted