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

On 01/04/2014 08:12 PM, Brian Barker wrote:
> 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
How about bringing the data from the other sheets to the first sheet, or 
to a 'work' sheet and calcing the data on that sheet. When you print, 
just don't print the work sheet.

John


--
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