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
