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

Exactly. All of the functions that I use seem to work across sheets, 
except AVERAGEIF and COUNTIF. I will look into filing a bug report. For 
now, is there any way to get the average of cell H35 across all of my 
sheets without adding other sheets or modifying my layout? I will have 
people using this form who struggle to understand what the start button 
is, and saying "print all except the last sheet" will blow their minds. 
Thanks for all of the help thus far. Oh, and the formula I tried when I 
found that AVERAGEIF would not work is below. It works except for COUNTIF.

=SUM(January.H35:December.H35)/(COUNT(January.H35:December.H35)-COUNTIF(January.H35:December.H35,"=0"))

-Ryan

On 1/5/2014 5:40 AM, John R. Sowden wrote:
> 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