[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
JLeite
General User

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

 Posted: Sat Jan 15, 2011 2:33 pm    Post subject: (Solved) Formulas differences 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/?5fit9dcs0u39pjzLast edited by JLeite on Fri Jan 28, 2011 8:22 am; edited 1 time in total
ken johnson
Super User

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

Posted: Sat Jan 15, 2011 3:56 pm    Post subject:

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
_________________
JLeite
General User

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

Posted: Fri Jan 28, 2011 9:01 am    Post subject:

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:
In Sheet2, cell B5 has a typical example of what I mean. In this cases how sumif works?
Best regards
JLeite
ken johnson
Super User

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

Posted: Fri Jan 28, 2011 1:51 pm    Post subject:

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
_________________
JLeite
General User

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

Posted: Fri Jan 28, 2011 3:49 pm    Post subject:

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
ken johnson
Super User

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

 Posted: Fri Jan 28, 2011 7:57 pm    Post subject: 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
JLeite
General User

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

Posted: Sat Jan 29, 2011 12:22 pm    Post subject:

 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
ken johnson
Super User

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

Posted: Sat Jan 29, 2011 1:46 pm    Post subject:

 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
JLeite
General User

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

Posted: Sat Jan 29, 2011 2:47 pm    Post subject:

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
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
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