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

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