2012 Archives by date, by thread · List index

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