All functions I tried worked cross-sheet except AVERAGEIF and COUNTIF, so I am assuming this is a bug. If AVERAGEIF would work this would be so simple. I am away from home right now but will send the formula tomorrow.
Sent from my Verizon Wireless 4G LTE smartphone -------- Original message -------- From: Brian Barker <[email protected]> Date:2014/01/04 23:12 (GMT-05:00) To: [email protected] Subject: Re: [libreoffice-users] Calc cross-sheet issue with COUNTIF and AVERAGEIF... At 19:10 04/01/2014 -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. This sounds a bug: AVERAGE() works on a cross-sheet range, so I don't see why AVERAGEIF() shouldn't. >I then tried the formula below, ... Er, I don't see a formula below ... >... which uses COUNTIF, but the part with COUNTIF in it causes a 504 also. Isn't that also a bug? At 03:28 05/01/2014 +0200, Paul Steyn 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+February.H35+...)/(January.H36+February.H36+...) >Of course you would need to type in all the actual cell references >instead of the ellipses. Since SUM() *does* work on cross-sheet ranges, you could simplify this to =SUM(January.H35:December.H35)/SUM(January.H36:December.H36) >Maybe someone else knows a way to do it using existing functions, ... Keep watching. At 21:38 04/01/2014 -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. That is no problem: the intermediate cells could be on other sheets or simply outside the print range. Another way of using intermediate values would be simply to have a range of twelve cells on your first sheet that simply contain =January.H35 and so on. If preferred, this range could be outside your print range - or even hidden. AVERAGEIF() should then work straightforwardly on this range of copies. >Is there a reason that AVERAGEIF and COUNTIF will not work with data >on other sheets? Not that I can see. >... is there any possible way to do this in LO? This is a messy workaround, but it appears to work: =SUM(January.H35:December.H35)/(COUNT(January.H35:December.H35)-FREQUENCY(January.H35:December.H35;0)) I trust this helps. Brian Barker -- 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 -- 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
