2014 Archives by date, by thread · List index

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