Re: [libreoffice-users] Calc: Average value, multiple conditions

On 2012-09-06 13:59, Johnny Rosenberg wrote:
> I have a spreadsheet with quite a lot of values and text.
> Column C: Name.
> Column D: Date.
> Column H: Values (integers, currently in the range of 176 to 605).
>
> R1: A specific name that matches many of the rows of column C.
> P2: Start date.
> Q2: End date.
>
> I tried this:
> {=SUM(($C$2:$C$1001=R$1)*($D$2:$D$1001>=$P2)*($D$2:$D$1001<$Q2)*$H$2:$H$1001)}
> Works great. Gives 29072 in my specific spreadsheet. Expected value: 29072.
>
> Now, I changed to:
> {=AVERAGE(($C$2:$C$1001=R$1)*($D$2:$D$1001>=$P2)*($D$2:$D$1001<$Q2)*$H$2:$H$1001)}
> Result: 52.29.
> Expected value: Somewhere around 450.
>
> Was I just too quick to assume that AVERAGE can be used in the same way as SUM?
>
>
>
>
>
> Kind regards
>
> Johnny Rosenberg
> ジョニー・ローゼンバーグ

Johnny:

You need to use this syntax to get an average value based on multiple 
criteria.

{=AVERAGE(IF(($C$2:$C$1001=R$1)*($D$2:$D$1001>=$P2)*($D$2:$D$1001<$Q2),$H$2:$H$1001))}

TomW

--
For unsubscribe instructions e-mail to: users+help@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