2014 Archives by date, by thread · List index

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

Well I have designed the sheets to be printed at the end of each year, so extra cells would not work. I do know Basic (I started my coding career using Basic on an Atari 400 in 1986) but would much rather use the built-in functions of Calc. Is there a reason that AVERAGEIF and COUNTIF will not work with data on other sheets? I would REALLY prefer for this to just work, rather than coding in more things that I will have to maintain in the future. In other words, is there any possible way to do this in LO? MSO can do this easily, but we switched to LO a few years back at my suggestion to save thousands of dollars and it has worked wonderfully for us, but Calc seems to have a few issues where Excel does not. I will say though, that this is the first issue I have been unable to solve on my own.
```
On 1/4/2014 8:28 PM, Paul 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+Fenruary.H35+...)/(January.H36+February.H36+...)". Of
course you would need to type in all the actual cell references instead
of the ellipses. Better yet would be to split that cover cell into
three cells, one for the sum of all H35 cells, one for the sum of all
H36 cells, and one for the averaging of those two numbers. I'm a fan of
having such intermediate calculations in the spreadsheet. Things get
much simpler (and therefore easier to debug and modify) when you
include intermediate steps and don't try to do too much in one cell.

If adding another cell (or cells) is out of the question, it could all
be done in a user function, say in LO Basic. That would require you to
code a user defined function, and I'm not sure how familiar you are
with that. Depending on your point of view, a user defined function
might be either a more or a less elegant solution, and more or less
difficult to change in future.

Maybe someone else knows a way to do it using existing functions, but I
can only think of those two approaches offhand.

Paul

On Sat, 04 Jan 2014 19:10:11 -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. I then tried the formula below,
which uses COUNTIF, but the part with COUNTIF in it causes a 504
also. If I cannot use COUNTIF or AVERAGEIF, how do I get an average
of cell H35 on each sheet where H35 is greater than zero?

```
```
```
```

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