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 <[email protected]> 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: [email protected] 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
