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

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 <ryana@reachtechfp.com> 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