2014 Archives by date, by thread · List index

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

```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 <b.m.barker@btinternet.com>
Date:2014/01/04  23:12  (GMT-05:00)
To: users@global.libreoffice.org
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
```
```
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

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

### Context

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