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