OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

(Solved) Formulas differences

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
JLeite
General User
General User


Joined: 04 Jan 2011
Posts: 15
Location: Porto, Portugal

PostPosted: Sat Jan 15, 2011 2:33 pm    Post subject: (Solved) Formulas differences Reply with quote

I normally use the following formula for conditional sum:
{=SUM(IF($D$5:$D$20=G9;$E$5:$E$20;""))}
instead of a more traditional:
=SUMIF($D$5:$D$20;G9;$E$5:$E$20)
The result seems to be the same in both cases and the first one seems more natural to me.
My doubts are:
Are they really identical?
If so, in very large spreadsheets where there are hundreds or thousands of rows and dozens of columns, is there any advantage in any of them in terms of calculation speed or used memory?
Best regards
JLeite


P.S. Here's a attachment with an example
http://www.mediafire.com/?5fit9dcs0u39pjz


Last edited by JLeite on Fri Jan 28, 2011 8:22 am; edited 1 time in total
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Sat Jan 15, 2011 3:56 pm    Post subject: Reply with quote

SUMIF is faster than {=SUM(IF(...))}
To illustrate the performance difference I used
Code:
=RANDBETWEEN(1;100)
to fill D1:D65535 with random integers from 1 to 100 then used Copy/Paste Special to convert the formulas to there number values.
Then I just put 1 into E1 and double-clicked its fill handle to fill E1:E65535 with 1,2,3,....65535.
Next I used
Code:
=RANDBETWEEN(1;100)
and Copy/Paste Special to fill G1:G500 with random integers from 1 to 100.
After that I entered the formula
Code:
=SUMIF($D$1:$D$65535;G1;$E$1:$E$65535)
into H1 then doubled-clicked its fill handle so that it would be filled down to H500.
During the calculation the mouse pointer changes to a spinning blue wheel and it went on for 7 revolutions before the calculations were completed.
I then backspaced out the SUMIF formulas in H1:H500 and array entered
Code:
=SUM(IF($D$1:$D$65535=G1;$E$1:$E$65535;""))
into H1.
This time, after I held down the Ctrl key and double-clicked H1's fill handle, the spinning blue wheel went on for 55 revolutions before calculation of the array formulas in H1:H500 were completed.
Array formulas should only be used when the same task is not possible with standard functions.

Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
JLeite
General User
General User


Joined: 04 Jan 2011
Posts: 15
Location: Porto, Portugal

PostPosted: Fri Jan 28, 2011 9:01 am    Post subject: Reply with quote

ken johnson wrote:
SUMIF is faster than {=SUM(IF(...))}
To illustrate the performance difference I used
Code:
=RANDBETWEEN(1;100)
to fill D1:D65535 with random integers from 1 to 100 then used Copy/Paste Special to convert the formulas to there number values.
Then I just put 1 into E1 and double-clicked its fill handle to fill E1:E65535 with 1,2,3,....65535.
Next I used
Code:
=RANDBETWEEN(1;100)
and Copy/Paste Special to fill G1:G500 with random integers from 1 to 100.
After that I entered the formula
Code:
=SUMIF($D$1:$D$65535;G1;$E$1:$E$65535)
into H1 then doubled-clicked its fill handle so that it would be filled down to H500.
During the calculation the mouse pointer changes to a spinning blue wheel and it went on for 7 revolutions before the calculations were completed.
I then backspaced out the SUMIF formulas in H1:H500 and array entered
Code:
=SUM(IF($D$1:$D$65535=G1;$E$1:$E$65535;""))
into H1.
This time, after I held down the Ctrl key and double-clicked H1's fill handle, the spinning blue wheel went on for 55 revolutions before calculation of the array formulas in H1:H500 were completed.
Array formulas should only be used when the same task is not possible with standard functions.

Ken Johnson


Thank you Ken.
After your explanation I've changed some of my spreadsheets. But I still have a problem.
Many times I need to make a conditional summation where I do several checks for example:
http://www.mediafire.com/download.php?4badprmegjbhy58
In Sheet2, cell B5 has a typical example of what I mean. In this cases how sumif works?
Best regards
JLeite
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Fri Jan 28, 2011 1:51 pm    Post subject: Reply with quote

SUMIF can only handle one condition.
SUMPRODUCT can handle up to 29 conditions when used to sum a column of values according to the criteria.
Try...
Code:
=SUMPRODUCT(Sheet1.F2:F1300=B1;Sheet1.G2:G1300=B2;Sheet1.H2:H1300=B3;Sheet1.I2:I1300=B4;Sheet1.J2:J1300)

SUMPRODUCT is a standard function, however, its performance can be slow because it works like an array function.

Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
JLeite
General User
General User


Joined: 04 Jan 2011
Posts: 15
Location: Porto, Portugal

PostPosted: Fri Jan 28, 2011 3:49 pm    Post subject: Reply with quote

ken johnson wrote:
SUMIF can only handle one condition.
SUMPRODUCT can handle up to 29 conditions when used to sum a column of values according to the criteria.
Try...
Code:
=SUMPRODUCT(Sheet1.F2:F1300=B1;Sheet1.G2:G1300=B2;Sheet1.H2:H1300=B3;Sheet1.I2:I1300=B4;Sheet1.J2:J1300)

SUMPRODUCT is a standard function, however, its performance can be slow because it works like an array function.

Ken Johnson


I think I got it.
As SUMPRODUCT only has a vector, it only sums and doesn't multiply anything, right?
JLeite
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Fri Jan 28, 2011 7:57 pm    Post subject: Reply with quote

Each parameter in the SUMPRODUCT function can be considered to be a vector.
For example, say there are 3 parameters, each having five vector components...
SUMPRODUCT({a1|a2|a3|a4|a5};{b1|b2|b3|b4|b5};{c1|c2|c3|c4|c5})
SUMPRODUCT calculates the sum of the products of the components...
a1*b1*c1+a2*b2*c2+a3*b3*c3+a4*b4*c4+a5*b5*c5

When the components of two of the three parameters are the results of tests for criteria, there values will be either TRUE (1) or FALSE (0) and the SUMPRODUCT result will be the sum of the third parameter's components that correspond to both criteria being TRUE.
Example...
SUMPRODUCT({1|0|1|0|1};{1|1|0|0|1};{c1|c2|c3|c4|c5})
=1*1*c1+0*1*c2+1*0*c3+0*0*c4+1*1*c5
=c1+0+0+0+c5
=c1+c5

Ken Johnson
Back to top
View user's profile Send private message
JLeite
General User
General User


Joined: 04 Jan 2011
Posts: 15
Location: Porto, Portugal

PostPosted: Sat Jan 29, 2011 12:22 pm    Post subject: Reply with quote

ken johnson wrote:
Each parameter in the SUMPRODUCT function can be considered to be a vector.
For example, say there are 3 parameters, each having five vector components...
SUMPRODUCT({a1|a2|a3|a4|a5};{b1|b2|b3|b4|b5};{c1|c2|c3|c4|c5})
SUMPRODUCT calculates the sum of the products of the components...
a1*b1*c1+a2*b2*c2+a3*b3*c3+a4*b4*c4+a5*b5*c5

When the components of two of the three parameters are the results of tests for criteria, there values will be either TRUE (1) or FALSE (0) and the SUMPRODUCT result will be the sum of the third parameter's components that correspond to both criteria being TRUE.
Example...
SUMPRODUCT({1|0|1|0|1};{1|1|0|0|1};{c1|c2|c3|c4|c5})
=1*1*c1+0*1*c2+1*0*c3+0*0*c4+1*1*c5
=c1+0+0+0+c5
=c1+c5

Ken Johnson


If I understand, when we compare a column with a value we also have a vector with zeros when it's false and ones when it's true, and the SUMPRODUCT makes the scalar product between vectors. Am I getting it?
JLeite
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Sat Jan 29, 2011 1:46 pm    Post subject: Reply with quote

JLeite wrote:
If I understand, when we compare a column with a value we also have a vector with zeros when it's false and ones when it's true, and the SUMPRODUCT makes the scalar product between vectors. Am I getting it?
JLeite

Sounds perfect!

Ken Johnson
Back to top
View user's profile Send private message
JLeite
General User
General User


Joined: 04 Jan 2011
Posts: 15
Location: Porto, Portugal

PostPosted: Sat Jan 29, 2011 2:47 pm    Post subject: Reply with quote

ken johnson wrote:
JLeite wrote:
If I understand, when we compare a column with a value we also have a vector with zeros when it's false and ones when it's true, and the SUMPRODUCT makes the scalar product between vectors. Am I getting it?
JLeite

Sounds perfect!

Ken Johnson


Ok. Thank you, Ken.
JLeite
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group