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: [email protected]
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
