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

This has been a most enlightening discussion.

I am a big fan of open source software. That said, this discussion has shined a bright light on one 
of the most problematic areas of the concept.

I might be totally off base here but as I view it, without a central management structure to 
_enforce_ standards two separate groups of developers may develop what appear to the end user as 
being similar related features that are inconsistent with each other. This results in unpleasant 
surprises for the end user.

In the instance discussed in this thread, functions such as SUM(), COUNT(), AVERAGE(), and probably 
FREQUENCY() have been developed with support for three dimensional (3D) ranges of cells (sheet, 
row, and column) while functions such as SUMIF(), COUNTIF(), and AVERAGEIF() were developed with 
support for only the more traditional two dimensional (2D) ranges of cells (row and column).

I have no idea how difficult it would be to implement the 3D paradigm for the xxxIF() functions but 
I know that it's exactly what I've wanted several times in the past without any concept as to the 
syntax that could be used that would make the functionality possible.

In this case the 3D paradigm can be fully implemented without breaking function calls already in 
.xsl documents even if Excel hasn't implemented the 3D paradigm so I hope it will be done in LO. 

Are developers reading what's posted here?

Ryan Ashley <ryana@reachtechfp.com> wrote:
> I read your solution, but do not want to alter any cells or add new
> ones 
> as the spreadsheets are formatted to fit onto a single sheet of paper.
> I 
> am going to test the solution using frequency right now. It looks 
> perfect to me, but I won't know until I put some data into the sheets 
> and see what happens. I will report back soon. Thank you all for your 
> suggestions and feedback!
>
> -Ryan

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

Nothing super messy about it though the 3D paradigm applied to AVERAGEIF() would indeed be cleaner.

-- 
Jim

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