| View previous topic :: View next topic |
| 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/?5fit9dcs0u39pjz
Last edited by JLeite on Fri Jan 28, 2011 8:22 am; edited 1 time in total |
|
| Back to top |
|
 |
ken johnson
Super User
Joined: 23 Apr 2009
Posts: 1875
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
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button). |
|
| Back to top |
|
|
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:
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 |
|
|
ken johnson
Super User
Joined: 23 Apr 2009
Posts: 1875
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
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button). |
|
| Back to top |
|
|
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 |
|
| Back to top |
|
|
ken johnson
Super User
Joined: 23 Apr 2009
Posts: 1875
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 |
|
| Back to top |
|
|
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 |
|
| Back to top |
|
|
ken johnson
Super User
Joined: 23 Apr 2009
Posts: 1875
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 |
|
| Back to top |
|
|
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 |
|
| Back to top |
|
|
|
