Increasing the speed of Sumproduct

Hi,

1. Just read http://www.mcgimpsey.com/excel/formulae/doubleneg.html

In this JEM says that we have double negs so that
=SUMPRODUCT(--(A1:A5>10),B1:B5))
can be coerced in to 1. As per JEM"s explanation single unary will coerce
True/False to Zero/One and the second double unary is used so that the
negative values could be converted to its original sign. My "reasoning" was
instead of using double negative sign why not use a single + sign and
achieve further speed increase.

So I did this =SUMPRODUCT(--(A1:A5>5),--(A1:A5<10),B1:B5) . The data I used
was
               A            B
      2 3
      3 4
      11 5
      5 6
      6 10


SADLY, got the answer as Zero. I used evaluate formula feature to see why
excel wasnt coercing true, false to 1,0 by seeing the "+" sign.
But couldnt understsand it. Why cant a single plus sign achieve that?

2.(I did not accept defeat with the above and now tried a diff. approach.).
Suppose the number of conditions to evaluate is 2. Like above  we are
checking for 2 conditions A1:A5>5 and A1:A5<10 and in all using 4 negs. I
think it can be reduced to 2 negs in the following way..

=SUMPRODUCT(-(A1:A5>5),-(A1:A5<10),B1:B5). This way when we have EVEN number
of conditions then use the unary operator only once for each condition so
that net effect of minus cancels out.

What if we have ODD number of conditions.? For that, for one condition we
will keep it as double negs and for the rest it will be single negs.

I think that there could be slightly more tweaking of even ODD condition.
Just lie EVEN case for all conditions take it as single neg and then in
front of sumproduct put a Negative sign in order to get the correct result
for the final evaluation.

Like suppose we have to check for 3 conditions, with  A1:A5>5, A1:A5<10 and
A1:A5> average(A1:A5)

then write the formula as
= -SUMPRODUCT(-(A1:A5>5),-(A1:A5<10),-(A1:A5> Average(A1:A5)),B1:B5).

Am I talking sense here?

Regards,
Hari
India


0
excel_hari (160)
8/14/2004 12:19:48 AM
excel.misc 78881 articles. 5 followers. Follow

11 Replies
651 Views

Similar Articles

[PageSpeed] 34

Yaar, Hari! Thanks a lot for your post...am a great fan of th
sumproduct function and have been using it very often in my financia
and budgeting models. 

That link explaining the "--" operator was excellent. I've always go
frustrated with Sumproduct's inability to handle text..but the doubl
unitary operator takes care of that, only thing is we have to use ",
instead of "*".

Happy Independence Day! :cool:  SAARE JAHAN SE ACCHA HINDUSTAN HAMARA!

Jai Hind!

Kavi

--
Message posted from http://www.ExcelForum.com

0
8/14/2004 4:41:33 AM
Hi Hari
to answer some of your questions:
1. a single '+' is not a mathematical operation. So Excel just 'skips'
the '+' in front of the boolean values and no coercion will take plase.
So you have to use either
--(...),
0+(...)
(...)^1
to create a real mathematical operation

2. Though you're correct with replacing the double minus with a single
minus in case of an even number of conditions (and using a single
double minus in case of an odd number of conditions) I would NOT do
thisfor the following reasons:
- I doubt you'll recognize any speed advantage in real life
- This is not very robust if you miss a single minus or just got the
number of conditions wrong.
So in total: as you won't gain (IMHO) a significant speed gain just use
the double minus (unaray) operator :-)


--
Regards
Frank Kabel
Frankfurt, Germany

"Hari" <excel_hari@hotmail.com> schrieb im Newsbeitrag
news:OimxqRZgEHA.3016@tk2msftngp13.phx.gbl...
> Hi,
>
> 1. Just read http://www.mcgimpsey.com/excel/formulae/doubleneg.html
>
> In this JEM says that we have double negs so that
> =SUMPRODUCT(--(A1:A5>10),B1:B5))
> can be coerced in to 1. As per JEM"s explanation single unary will
coerce
> True/False to Zero/One and the second double unary is used so that
the
> negative values could be converted to its original sign. My
"reasoning" was
> instead of using double negative sign why not use a single + sign and
> achieve further speed increase.
>
> So I did this =SUMPRODUCT(--(A1:A5>5),--(A1:A5<10),B1:B5) . The data
I used
> was
>                A            B
>       2 3
>       3 4
>       11 5
>       5 6
>       6 10
>
>
> SADLY, got the answer as Zero. I used evaluate formula feature to see
why
> excel wasnt coercing true, false to 1,0 by seeing the "+" sign.
> But couldnt understsand it. Why cant a single plus sign achieve that?
>
> 2.(I did not accept defeat with the above and now tried a diff.
approach.).
> Suppose the number of conditions to evaluate is 2. Like above  we are
> checking for 2 conditions A1:A5>5 and A1:A5<10 and in all using 4
negs. I
> think it can be reduced to 2 negs in the following way..
>
> =SUMPRODUCT(-(A1:A5>5),-(A1:A5<10),B1:B5). This way when we have EVEN
number
> of conditions then use the unary operator only once for each
condition so
> that net effect of minus cancels out.
>
> What if we have ODD number of conditions.? For that, for one
condition we
> will keep it as double negs and for the rest it will be single negs.
>
> I think that there could be slightly more tweaking of even ODD
condition.
> Just lie EVEN case for all conditions take it as single neg and then
in
> front of sumproduct put a Negative sign in order to get the correct
result
> for the final evaluation.
>
> Like suppose we have to check for 3 conditions, with  A1:A5>5,
A1:A5<10 and
> A1:A5> average(A1:A5)
>
> then write the formula as
> = -SUMPRODUCT(-(A1:A5>5),-(A1:A5<10),-(A1:A5> Average(A1:A5)),B1:B5).
>
> Am I talking sense here?
>
> Regards,
> Hari
> India
>
>

0
frank.kabel (11126)
8/14/2004 8:00:55 AM
Hi Hari -

I'm not a sumproduct expert and can't give you advice regarding singl
as opposed to double unary.

However, just out of curiosity, I copied your original formula and dat
to a worksheet and my answer was 10.  Are you sure you entered i
correctly

--
Message posted from http://www.ExcelForum.com

0
8/14/2004 9:07:43 PM
Hi Frank,

Thanx for your explanation regarding point 1.

Actually I have a file in which there are lots and lots of Sumproduct's. As
of today close to 50000 and it is made up of multiple condition checking
based on data from another worksheet. The no of sumproducts formulas
increase by 250 every day (as one of the conditions is based on date so with
each new day the range of formula copying also increases).

Now the above file is for one site and one project of our company. I have
similar format of files for  every project in every site which amounts to 20
more.

A single file takes around 25 minutes to calculate in a P4 1.6 GHz,  256 MB
RAM, Win 2000. Size of a single file is 7.5 MB as of today

Presently I set the calculation to manual before opening this file. Problem
happens when I mail/share this file with others.Because unsuspectingly they
open the file and excel hangs as their calculation would be in automatic. I
understand that even if i change the formula there may not be much of an
advantage in speed gains (inspite of the large no of sumproducts). Frankly
speaking I havent done any speed test on what happens If I change the
formula. ( due to some issues..)

As I said that Im having problem in sharing this file with other users of
the output from this file. I was thinking what if i write the formulas in VB
code and then write the end results of those formula evaluation in to the
cells. That way only when the Macro is run will the calculation happen. This
way anybody who opens the file will not be caught unawares.

But I see one problem with this approach of using VB code and that is
"auditing" of formula will become "difficult". I mean that there are quite a
number of people who will be comfortable in seeing the formula before their
eyes and see whether the ranges and conditions are correct. But if I use the
VB code, then people will not have much faith in the output (also majority
arent comfortable with VB etc).

So, I wanted to know whether u could offer some perspective/ideas on this.

Regards,
Hari
India
"Frank Kabel" <frank.kabel@freenet.de> wrote in message
news:OdPK1SdgEHA.2984@tk2msftngp13.phx.gbl...
> Hi Hari
> to answer some of your questions:
> 1. a single '+' is not a mathematical operation. So Excel just 'skips'
> the '+' in front of the boolean values and no coercion will take plase.
> So you have to use either
> --(...),
> 0+(...)
> (...)^1
> to create a real mathematical operation
>
> 2. Though you're correct with replacing the double minus with a single
> minus in case of an even number of conditions (and using a single
> double minus in case of an odd number of conditions) I would NOT do
> thisfor the following reasons:
> - I doubt you'll recognize any speed advantage in real life
> - This is not very robust if you miss a single minus or just got the
> number of conditions wrong.
> So in total: as you won't gain (IMHO) a significant speed gain just use
> the double minus (unaray) operator :-)
>
>
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
>
> "Hari" <excel_hari@hotmail.com> schrieb im Newsbeitrag
> news:OimxqRZgEHA.3016@tk2msftngp13.phx.gbl...
> > Hi,
> >
> > 1. Just read http://www.mcgimpsey.com/excel/formulae/doubleneg.html
> >
> > In this JEM says that we have double negs so that
> > =SUMPRODUCT(--(A1:A5>10),B1:B5))
> > can be coerced in to 1. As per JEM"s explanation single unary will
> coerce
> > True/False to Zero/One and the second double unary is used so that
> the
> > negative values could be converted to its original sign. My
> "reasoning" was
> > instead of using double negative sign why not use a single + sign and
> > achieve further speed increase.
> >
> > So I did this =SUMPRODUCT(--(A1:A5>5),--(A1:A5<10),B1:B5) . The data
> I used
> > was
> >                A            B
> >       2 3
> >       3 4
> >       11 5
> >       5 6
> >       6 10
> >
> >
> > SADLY, got the answer as Zero. I used evaluate formula feature to see
> why
> > excel wasnt coercing true, false to 1,0 by seeing the "+" sign.
> > But couldnt understsand it. Why cant a single plus sign achieve that?
> >
> > 2.(I did not accept defeat with the above and now tried a diff.
> approach.).
> > Suppose the number of conditions to evaluate is 2. Like above  we are
> > checking for 2 conditions A1:A5>5 and A1:A5<10 and in all using 4
> negs. I
> > think it can be reduced to 2 negs in the following way..
> >
> > =SUMPRODUCT(-(A1:A5>5),-(A1:A5<10),B1:B5). This way when we have EVEN
> number
> > of conditions then use the unary operator only once for each
> condition so
> > that net effect of minus cancels out.
> >
> > What if we have ODD number of conditions.? For that, for one
> condition we
> > will keep it as double negs and for the rest it will be single negs.
> >
> > I think that there could be slightly more tweaking of even ODD
> condition.
> > Just lie EVEN case for all conditions take it as single neg and then
> in
> > front of sumproduct put a Negative sign in order to get the correct
> result
> > for the final evaluation.
> >
> > Like suppose we have to check for 3 conditions, with  A1:A5>5,
> A1:A5<10 and
> > A1:A5> average(A1:A5)
> >
> > then write the formula as
> > = -SUMPRODUCT(-(A1:A5>5),-(A1:A5<10),-(A1:A5> Average(A1:A5)),B1:B5).
> >
> > Am I talking sense here?
> >
> > Regards,
> > Hari
> > India
> >
> >
>


0
excel_hari (160)
8/16/2004 3:04:32 PM
Hi
having so many calculations in one workbook would lead me to thinking
that maybe Excel is not the right tool for this. As I don't know what
you're trying to do with this it is difficult to say though.

But IMHO shared Excel files are nearly always not a good idea :-)

--
Regards
Frank Kabel
Frankfurt, Germany

"Hari" <excel_hari@hotmail.com> schrieb im Newsbeitrag
news:#FcOYJ6gEHA.3548@TK2MSFTNGP09.phx.gbl...
> Hi Frank,
>
> Thanx for your explanation regarding point 1.
>
> Actually I have a file in which there are lots and lots of
Sumproduct's. As
> of today close to 50000 and it is made up of multiple condition
checking
> based on data from another worksheet. The no of sumproducts formulas
> increase by 250 every day (as one of the conditions is based on date
so with
> each new day the range of formula copying also increases).
>
> Now the above file is for one site and one project of our company. I
have
> similar format of files for  every project in every site which
amounts to 20
> more.
>
> A single file takes around 25 minutes to calculate in a P4 1.6 GHz,
256 MB
> RAM, Win 2000. Size of a single file is 7.5 MB as of today
>
> Presently I set the calculation to manual before opening this file.
Problem
> happens when I mail/share this file with others.Because
unsuspectingly they
> open the file and excel hangs as their calculation would be in
automatic. I
> understand that even if i change the formula there may not be much of
an
> advantage in speed gains (inspite of the large no of sumproducts).
Frankly
> speaking I havent done any speed test on what happens If I change the
> formula. ( due to some issues..)
>
> As I said that Im having problem in sharing this file with other
users of
> the output from this file. I was thinking what if i write the
formulas in VB
> code and then write the end results of those formula evaluation in to
the
> cells. That way only when the Macro is run will the calculation
happen. This
> way anybody who opens the file will not be caught unawares.
>
> But I see one problem with this approach of using VB code and that is
> "auditing" of formula will become "difficult". I mean that there are
quite a
> number of people who will be comfortable in seeing the formula before
their
> eyes and see whether the ranges and conditions are correct. But if I
use the
> VB code, then people will not have much faith in the output (also
majority
> arent comfortable with VB etc).
>
> So, I wanted to know whether u could offer some perspective/ideas on
this.
>
> Regards,
> Hari
> India
> "Frank Kabel" <frank.kabel@freenet.de> wrote in message
> news:OdPK1SdgEHA.2984@tk2msftngp13.phx.gbl...
> > Hi Hari
> > to answer some of your questions:
> > 1. a single '+' is not a mathematical operation. So Excel just
'skips'
> > the '+' in front of the boolean values and no coercion will take
plase.
> > So you have to use either
> > --(...),
> > 0+(...)
> > (...)^1
> > to create a real mathematical operation
> >
> > 2. Though you're correct with replacing the double minus with a
single
> > minus in case of an even number of conditions (and using a single
> > double minus in case of an odd number of conditions) I would NOT do
> > thisfor the following reasons:
> > - I doubt you'll recognize any speed advantage in real life
> > - This is not very robust if you miss a single minus or just got
the
> > number of conditions wrong.
> > So in total: as you won't gain (IMHO) a significant speed gain just
use
> > the double minus (unaray) operator :-)
> >
> >
> > --
> > Regards
> > Frank Kabel
> > Frankfurt, Germany
> >
> > "Hari" <excel_hari@hotmail.com> schrieb im Newsbeitrag
> > news:OimxqRZgEHA.3016@tk2msftngp13.phx.gbl...
> > > Hi,
> > >
> > > 1. Just read
http://www.mcgimpsey.com/excel/formulae/doubleneg.html
> > >
> > > In this JEM says that we have double negs so that
> > > =SUMPRODUCT(--(A1:A5>10),B1:B5))
> > > can be coerced in to 1. As per JEM"s explanation single unary
will
> > coerce
> > > True/False to Zero/One and the second double unary is used so
that
> > the
> > > negative values could be converted to its original sign. My
> > "reasoning" was
> > > instead of using double negative sign why not use a single + sign
and
> > > achieve further speed increase.
> > >
> > > So I did this =SUMPRODUCT(--(A1:A5>5),--(A1:A5<10),B1:B5) . The
data
> > I used
> > > was
> > >                A            B
> > >       2 3
> > >       3 4
> > >       11 5
> > >       5 6
> > >       6 10
> > >
> > >
> > > SADLY, got the answer as Zero. I used evaluate formula feature to
see
> > why
> > > excel wasnt coercing true, false to 1,0 by seeing the "+" sign.
> > > But couldnt understsand it. Why cant a single plus sign achieve
that?
> > >
> > > 2.(I did not accept defeat with the above and now tried a diff.
> > approach.).
> > > Suppose the number of conditions to evaluate is 2. Like above  we
are
> > > checking for 2 conditions A1:A5>5 and A1:A5<10 and in all using 4
> > negs. I
> > > think it can be reduced to 2 negs in the following way..
> > >
> > > =SUMPRODUCT(-(A1:A5>5),-(A1:A5<10),B1:B5). This way when we have
EVEN
> > number
> > > of conditions then use the unary operator only once for each
> > condition so
> > > that net effect of minus cancels out.
> > >
> > > What if we have ODD number of conditions.? For that, for one
> > condition we
> > > will keep it as double negs and for the rest it will be single
negs.
> > >
> > > I think that there could be slightly more tweaking of even ODD
> > condition.
> > > Just lie EVEN case for all conditions take it as single neg and
then
> > in
> > > front of sumproduct put a Negative sign in order to get the
correct
> > result
> > > for the final evaluation.
> > >
> > > Like suppose we have to check for 3 conditions, with  A1:A5>5,
> > A1:A5<10 and
> > > A1:A5> average(A1:A5)
> > >
> > > then write the formula as
> > > = -SUMPRODUCT(-(A1:A5>5),-(A1:A5<10),-(A1:A5>
Average(A1:A5)),B1:B5).
> > >
> > > Am I talking sense here?
> > >
> > > Regards,
> > > Hari
> > > India
> > >
> > >
> >
>
>

0
frank.kabel (11126)
8/16/2004 3:14:48 PM
Hi Frank,

I think ur right about Excel not being the right tool. May be Access, only
problem is I dont know access and think it would take too much time for me
to catch up...

Anyway thanx for ur help.

(Actually I havent set the file in to shared mode. When im through with
dumping the data and "running" the sumproducts and getting the output I mail
it to those who need to use the output/results)

Regards,
Hari
India

"Frank Kabel" <frank.kabel@freenet.de> wrote in message
news:OAbsmO6gEHA.3016@tk2msftngp13.phx.gbl...
> Hi
> having so many calculations in one workbook would lead me to thinking
> that maybe Excel is not the right tool for this. As I don't know what
> you're trying to do with this it is difficult to say though.
>
> But IMHO shared Excel files are nearly always not a good idea :-)
>
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
>
> "Hari" <excel_hari@hotmail.com> schrieb im Newsbeitrag
> news:#FcOYJ6gEHA.3548@TK2MSFTNGP09.phx.gbl...
> > Hi Frank,
> >
> > Thanx for your explanation regarding point 1.
> >
> > Actually I have a file in which there are lots and lots of
> Sumproduct's. As
> > of today close to 50000 and it is made up of multiple condition
> checking
> > based on data from another worksheet. The no of sumproducts formulas
> > increase by 250 every day (as one of the conditions is based on date
> so with
> > each new day the range of formula copying also increases).
> >
> > Now the above file is for one site and one project of our company. I
> have
> > similar format of files for  every project in every site which
> amounts to 20
> > more.
> >
> > A single file takes around 25 minutes to calculate in a P4 1.6 GHz,
> 256 MB
> > RAM, Win 2000. Size of a single file is 7.5 MB as of today
> >
> > Presently I set the calculation to manual before opening this file.
> Problem
> > happens when I mail/share this file with others.Because
> unsuspectingly they
> > open the file and excel hangs as their calculation would be in
> automatic. I
> > understand that even if i change the formula there may not be much of
> an
> > advantage in speed gains (inspite of the large no of sumproducts).
> Frankly
> > speaking I havent done any speed test on what happens If I change the
> > formula. ( due to some issues..)
> >
> > As I said that Im having problem in sharing this file with other
> users of
> > the output from this file. I was thinking what if i write the
> formulas in VB
> > code and then write the end results of those formula evaluation in to
> the
> > cells. That way only when the Macro is run will the calculation
> happen. This
> > way anybody who opens the file will not be caught unawares.
> >
> > But I see one problem with this approach of using VB code and that is
> > "auditing" of formula will become "difficult". I mean that there are
> quite a
> > number of people who will be comfortable in seeing the formula before
> their
> > eyes and see whether the ranges and conditions are correct. But if I
> use the
> > VB code, then people will not have much faith in the output (also
> majority
> > arent comfortable with VB etc).
> >
> > So, I wanted to know whether u could offer some perspective/ideas on
> this.
> >
> > Regards,
> > Hari
> > India
> > "Frank Kabel" <frank.kabel@freenet.de> wrote in message
> > news:OdPK1SdgEHA.2984@tk2msftngp13.phx.gbl...
> > > Hi Hari
> > > to answer some of your questions:
> > > 1. a single '+' is not a mathematical operation. So Excel just
> 'skips'
> > > the '+' in front of the boolean values and no coercion will take
> plase.
> > > So you have to use either
> > > --(...),
> > > 0+(...)
> > > (...)^1
> > > to create a real mathematical operation
> > >
> > > 2. Though you're correct with replacing the double minus with a
> single
> > > minus in case of an even number of conditions (and using a single
> > > double minus in case of an odd number of conditions) I would NOT do
> > > thisfor the following reasons:
> > > - I doubt you'll recognize any speed advantage in real life
> > > - This is not very robust if you miss a single minus or just got
> the
> > > number of conditions wrong.
> > > So in total: as you won't gain (IMHO) a significant speed gain just
> use
> > > the double minus (unaray) operator :-)
> > >
> > >
> > > --
> > > Regards
> > > Frank Kabel
> > > Frankfurt, Germany
> > >
> > > "Hari" <excel_hari@hotmail.com> schrieb im Newsbeitrag
> > > news:OimxqRZgEHA.3016@tk2msftngp13.phx.gbl...
> > > > Hi,
> > > >
> > > > 1. Just read
> http://www.mcgimpsey.com/excel/formulae/doubleneg.html
> > > >
> > > > In this JEM says that we have double negs so that
> > > > =SUMPRODUCT(--(A1:A5>10),B1:B5))
> > > > can be coerced in to 1. As per JEM"s explanation single unary
> will
> > > coerce
> > > > True/False to Zero/One and the second double unary is used so
> that
> > > the
> > > > negative values could be converted to its original sign. My
> > > "reasoning" was
> > > > instead of using double negative sign why not use a single + sign
> and
> > > > achieve further speed increase.
> > > >
> > > > So I did this =SUMPRODUCT(--(A1:A5>5),--(A1:A5<10),B1:B5) . The
> data
> > > I used
> > > > was
> > > >                A            B
> > > >       2 3
> > > >       3 4
> > > >       11 5
> > > >       5 6
> > > >       6 10
> > > >
> > > >
> > > > SADLY, got the answer as Zero. I used evaluate formula feature to
> see
> > > why
> > > > excel wasnt coercing true, false to 1,0 by seeing the "+" sign.
> > > > But couldnt understsand it. Why cant a single plus sign achieve
> that?
> > > >
> > > > 2.(I did not accept defeat with the above and now tried a diff.
> > > approach.).
> > > > Suppose the number of conditions to evaluate is 2. Like above  we
> are
> > > > checking for 2 conditions A1:A5>5 and A1:A5<10 and in all using 4
> > > negs. I
> > > > think it can be reduced to 2 negs in the following way..
> > > >
> > > > =SUMPRODUCT(-(A1:A5>5),-(A1:A5<10),B1:B5). This way when we have
> EVEN
> > > number
> > > > of conditions then use the unary operator only once for each
> > > condition so
> > > > that net effect of minus cancels out.
> > > >
> > > > What if we have ODD number of conditions.? For that, for one
> > > condition we
> > > > will keep it as double negs and for the rest it will be single
> negs.
> > > >
> > > > I think that there could be slightly more tweaking of even ODD
> > > condition.
> > > > Just lie EVEN case for all conditions take it as single neg and
> then
> > > in
> > > > front of sumproduct put a Negative sign in order to get the
> correct
> > > result
> > > > for the final evaluation.
> > > >
> > > > Like suppose we have to check for 3 conditions, with  A1:A5>5,
> > > A1:A5<10 and
> > > > A1:A5> average(A1:A5)
> > > >
> > > > then write the formula as
> > > > = -SUMPRODUCT(-(A1:A5>5),-(A1:A5<10),-(A1:A5>
> Average(A1:A5)),B1:B5).
> > > >
> > > > Am I talking sense here?
> > > >
> > > > Regards,
> > > > Hari
> > > > India
> > > >
> > > >
> > >
> >
> >
>


0
excel_hari (160)
8/16/2004 3:30:54 PM
Hi Elsie,

I meant to write it as =SUMPRODUCT(+(A1:A5>5),+(A1:A5<10),B1:B5) but slip of
my finger ended up writing it as
=SUMPRODUCT(--(A1:A5>5),--(A1:A5<10),B1:B5).

Regards,
Hari
India

"ElsiePOA >" <<ElsiePOA.1azyet@excelforum-nospam.com> wrote in message
news:ElsiePOA.1azyet@excelforum-nospam.com...
> Hi Hari -
>
> I'm not a sumproduct expert and can't give you advice regarding single
> as opposed to double unary.
>
> However, just out of curiosity, I copied your original formula and data
> to a worksheet and my answer was 10.  Are you sure you entered it
> correctly?
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
excel_hari (160)
8/16/2004 3:35:43 PM
I would think that your spreadsheet could be made significantly more 
efficient, though not necessarily efficient enough to matter.

Techniques such as intermediate calculations (e.g., if multiple 
SUMPRODUCTs depend on the same conditions, precalculating the conditions 
may save recalcs), dynamic ranges where appropriate, etc. can 
significantly reduce calc time. See

    http://www.decisionmodels.com/optspeed.htm


In article <#FcOYJ6gEHA.3548@TK2MSFTNGP09.phx.gbl>,
 "Hari" <excel_hari@hotmail.com> wrote:

> Actually I have a file in which there are lots and lots of Sumproduct's. As
> of today close to 50000 and it is made up of multiple condition checking
> based on data from another worksheet. The no of sumproducts formulas
> increase by 250 every day (as one of the conditions is based on date so with
> each new day the range of formula copying also increases).
0
jemcgimpsey (6723)
8/16/2004 4:01:48 PM
Hi Frank,

On second thought ...

I understand that Unary operator was "introduced" so that number of
calculation steps reduce by one for each condition as compared to normal *.

If that reduction in time was considered reasonable enough to warrant a
change then why are u saying that skipping one unary would "I doubt you'll
recognize any speed advantage in real life"

I understand ur point about robustness but not able to appreciate  "if you
miss a single minus or just got the number of conditions wrong." I believe
that even if somebody misses a double minus in case of ODD number of
conditions due to wrong enumeration etc. then ideally it shouldnt matter
much as Sumproduct would yield a Negative answer and I believe that if we
are doing a strictly "count" cases kind of job then the count has to be
positive so wouldnt a negative answer be a flag for the person writing the
formula.

Regards,
Hari
India

"Frank Kabel" <frank.kabel@freenet.de> wrote in message
news:OdPK1SdgEHA.2984@tk2msftngp13.phx.gbl...
> Hi Hari
> to answer some of your questions:
> 1. a single '+' is not a mathematical operation. So Excel just 'skips'
> the '+' in front of the boolean values and no coercion will take plase.
> So you have to use either
> --(...),
> 0+(...)
> (...)^1
> to create a real mathematical operation
>
> 2. Though you're correct with replacing the double minus with a single
> minus in case of an even number of conditions (and using a single
> double minus in case of an odd number of conditions) I would NOT do
> thisfor the following reasons:
> - I doubt you'll recognize any speed advantage in real life
> - This is not very robust if you miss a single minus or just got the
> number of conditions wrong.
> So in total: as you won't gain (IMHO) a significant speed gain just use
> the double minus (unaray) operator :-)
>
>
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
>
> "Hari" <excel_hari@hotmail.com> schrieb im Newsbeitrag
> news:OimxqRZgEHA.3016@tk2msftngp13.phx.gbl...
> > Hi,
> >
> > 1. Just read http://www.mcgimpsey.com/excel/formulae/doubleneg.html
> >
> > In this JEM says that we have double negs so that
> > =SUMPRODUCT(--(A1:A5>10),B1:B5))
> > can be coerced in to 1. As per JEM"s explanation single unary will
> coerce
> > True/False to Zero/One and the second double unary is used so that
> the
> > negative values could be converted to its original sign. My
> "reasoning" was
> > instead of using double negative sign why not use a single + sign and
> > achieve further speed increase.
> >
> > So I did this =SUMPRODUCT(--(A1:A5>5),--(A1:A5<10),B1:B5) . The data
> I used
> > was
> >                A            B
> >       2 3
> >       3 4
> >       11 5
> >       5 6
> >       6 10
> >
> >
> > SADLY, got the answer as Zero. I used evaluate formula feature to see
> why
> > excel wasnt coercing true, false to 1,0 by seeing the "+" sign.
> > But couldnt understsand it. Why cant a single plus sign achieve that?
> >
> > 2.(I did not accept defeat with the above and now tried a diff.
> approach.).
> > Suppose the number of conditions to evaluate is 2. Like above  we are
> > checking for 2 conditions A1:A5>5 and A1:A5<10 and in all using 4
> negs. I
> > think it can be reduced to 2 negs in the following way..
> >
> > =SUMPRODUCT(-(A1:A5>5),-(A1:A5<10),B1:B5). This way when we have EVEN
> number
> > of conditions then use the unary operator only once for each
> condition so
> > that net effect of minus cancels out.
> >
> > What if we have ODD number of conditions.? For that, for one
> condition we
> > will keep it as double negs and for the rest it will be single negs.
> >
> > I think that there could be slightly more tweaking of even ODD
> condition.
> > Just lie EVEN case for all conditions take it as single neg and then
> in
> > front of sumproduct put a Negative sign in order to get the correct
> result
> > for the final evaluation.
> >
> > Like suppose we have to check for 3 conditions, with  A1:A5>5,
> A1:A5<10 and
> > A1:A5> average(A1:A5)
> >
> > then write the formula as
> > = -SUMPRODUCT(-(A1:A5>5),-(A1:A5<10),-(A1:A5> Average(A1:A5)),B1:B5).
> >
> > Am I talking sense here?
> >
> > Regards,
> > Hari
> > India
> >
> >
>


0
excel_hari (160)
8/16/2004 7:54:43 PM
Hi Hari
this may be true for conditional counts but is not that obvious for
conditional sums (as they could be of course negative). But again: I
think you probably won't see that much difference regarding speed (my
assumption -> but you may try it on your large sheet).

In addition also the 'speed advantage' of the unary operator is not
really recognizable in most real-life spreadsheets (maybe this is also
different for your spreadsheet with >5000 Sumproduct formulas). For me
it is more a personal taste and before I would try to optimise these
kind of formulas I would consider different approaches (like JE
presented in his post).

Regarding your specific spreadsheet: Have you considered using pivot
tables. In some cases they could be a replacement for Sumproduct
formulas



--
Regards
Frank Kabel
Frankfurt, Germany

"Hari" <excel_hari@hotmail.com> schrieb im Newsbeitrag
news:uzQSir8gEHA.3988@tk2msftngp13.phx.gbl...
> Hi Frank,
>
> On second thought ...
>
> I understand that Unary operator was "introduced" so that number of
> calculation steps reduce by one for each condition as compared to
normal *.
>
> If that reduction in time was considered reasonable enough to warrant
a
> change then why are u saying that skipping one unary would "I doubt
you'll
> recognize any speed advantage in real life"
>
> I understand ur point about robustness but not able to appreciate
"if you
> miss a single minus or just got the number of conditions wrong." I
believe
> that even if somebody misses a double minus in case of ODD number of
> conditions due to wrong enumeration etc. then ideally it shouldnt
matter
> much as Sumproduct would yield a Negative answer and I believe that
if we
> are doing a strictly "count" cases kind of job then the count has to
be
> positive so wouldnt a negative answer be a flag for the person
writing the
> formula.
>
> Regards,
> Hari
> India
>

0
frank.kabel (11126)
8/16/2004 8:05:14 PM
Hi Frank,

Thanx for reply.

I have actually tried using JE's approach whereby common conditions are
calculated only once for that row  and . But due to the NATURE of DATA and
CONDITION the file size was growing and growing without a limit (20 MB and
above). Charles taught me this method of grouping the conditions together
around 2 months back and I was very enthused initially but had to discard it
due to file size. (Though in the resulting file speed was better I chose to
not change). For me the compromise of speed is better than file size (as the
file has to be mailed etc).

Pivot had somewhat a similar problem of file size.

Now, I think I will turn to VB and "Dump" the result directly in to the
respective cells.

(I have 50000 SP's not 5000..)

Regards,
Hari
India

"Frank Kabel" <frank.kabel@freenet.de> wrote in message
news:%234Hc5w8gEHA.3264@tk2msftngp13.phx.gbl...
> Hi Hari
> this may be true for conditional counts but is not that obvious for
> conditional sums (as they could be of course negative). But again: I
> think you probably won't see that much difference regarding speed (my
> assumption -> but you may try it on your large sheet).
>
> In addition also the 'speed advantage' of the unary operator is not
> really recognizable in most real-life spreadsheets (maybe this is also
> different for your spreadsheet with >5000 Sumproduct formulas). For me
> it is more a personal taste and before I would try to optimise these
> kind of formulas I would consider different approaches (like JE
> presented in his post).
>
> Regarding your specific spreadsheet: Have you considered using pivot
> tables. In some cases they could be a replacement for Sumproduct
> formulas
>
>
>
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
>
> "Hari" <excel_hari@hotmail.com> schrieb im Newsbeitrag
> news:uzQSir8gEHA.3988@tk2msftngp13.phx.gbl...
> > Hi Frank,
> >
> > On second thought ...
> >
> > I understand that Unary operator was "introduced" so that number of
> > calculation steps reduce by one for each condition as compared to
> normal *.
> >
> > If that reduction in time was considered reasonable enough to warrant
> a
> > change then why are u saying that skipping one unary would "I doubt
> you'll
> > recognize any speed advantage in real life"
> >
> > I understand ur point about robustness but not able to appreciate
> "if you
> > miss a single minus or just got the number of conditions wrong." I
> believe
> > that even if somebody misses a double minus in case of ODD number of
> > conditions due to wrong enumeration etc. then ideally it shouldnt
> matter
> > much as Sumproduct would yield a Negative answer and I believe that
> if we
> > are doing a strictly "count" cases kind of job then the count has to
> be
> > positive so wouldnt a negative answer be a flag for the person
> writing the
> > formula.
> >
> > Regards,
> > Hari
> > India
> >
>


0
excel_hari (160)
8/16/2004 8:27:49 PM
Reply:

Similar Artilces:

Increase size of pie chart within control?
Is there a way to increase the size of a pie chart within its control? I have a limited amount of space in which to display the chart, so the overall size of the control is restricted. But within it there's quite a lot of "wasted" white space and I would like to make the pie itself larger and more legible. Can do, or not? Many thanks CW In design mode, double-click on the chart within the control to select it for editing. Then use the sizing handles to resize the chart area to the size of the control; this does not have to be square. Click on the plot area to select it ...

CRM 3.0 Speed and Performance Contacts and Accounts
We are facing a problem like discussed in CRM 3.0 Slow Web Interface since today. We upgraded CRM 1.2 to 3.0 everything was working fast for about 5 days and now we have from 1 second to the other the problem, that Contacts and Accounts needs more as 1 Minute to load. System Performance ist more or less 0. Any Idee what's causing this problem? From seconds to load up to minutes. !! It is after the first startup not because of JIT initializing !! Any idea? try thisw once...reset your IIS and see if it improves your performance. We've started to notice this on our own system as wel...

Drivers for percentage increase
I am trying to set up a driver to include in my budget profile so that my managers can select the percentage from a driver and see on each budget line what the outcome will be i.e staff costs may be calculated at 150,000 but if I used my driver to calculate an increase of 3.5% that would change the staff costs to 155,250. Can anyone help? Regards Jo I am not sure what you question is but this may help:- In cell A 2 I have 150,000 In cell B 2 I have 3.5% In cell C 2 I have:- =(A2*B2)+A2 - and this returns 155,250 to cell C 2. If my comments have helped please h...

to increase quantity
hello everybody, i have a question: i want to use project to calculate the delivery time of a machine component. I know the bill of material and the time of every step (manufacturing, mounting, etc.). I don't have problem to create a gantt for this item: but i have problem when i want to create a gantt for a number greater than one of my item. I think that i have to use a function that increase the duration of each step of the process, but i don't know how. Please help me. thank you -- sentenza ------------------------------------------------------------------------ s...

Date Increase
I currently have a form for entering current passwords for systems. I have a field to enter the date the password was last changed, and another field for the date the password expires, which is every 30 days. Is there anyway i can set the date password expires field to update automatically? The 2nd field is just calculated from the 1st and better left as a calculation in a query rather than a field in a table. There are no triggers at the table level in Access. Dan Wood wrote: >I currently have a form for entering current passwords for systems. I have a >field to enter...

can I increase excel column numbers to more than 256?
I am trying to analyse tables including more than 256 variables. I cannot transpose them into rows because the program i am using in association with excel (xlstat) takes the columns as the variables. Is there a way to increase the number of the columns?? or is it a fixed specification of the excel worksheet? thank for any help! Yes, it is fixed - you cannot increase the number of columns. You can, of course, split your data up into chunks of 250 columns - the first block of data on row 1, for example, then the next set starting on another row etc. Pete ...

Increasing alphabectically
Windows XP and Excel 2003 The first time through the program should read: With Active Sheet .First = Sheets("Rates").Range("B2") .Second = Sheets("Rates").Range("B3:B186") End With The next time through the Ranges should read "C2" and "C3:C186" Then next time "D2" and "D3:D186" and so on until it reaches "X2"and "X3:X186" Can someone suggest a good way to do this. Please. You could use index values rather than th...

Increasing Months
Hi Here is a simple question and I cannot find the answer. On a report I have a text box [Text301] and when display I see Fev-07 I would like to add something like 35 other text box so month would increase by 1 What could be the formula for [Text302] to display Mar-07 or Mar 2007. The formula should be issued from [Text301] Then [Text303] related to [Text302], [Text304] related to [Text303], ....... So the day I change [Text301] for let say Apr-08 [Text302] would give me May-08 [Text303} would be Jun-08, etc... I dont realy care with the format; it can be Mar 08 or Mar-08 or Mar-2...

Using CSocket with CSocketFile Increases memory usage
Hello All, I am using CSocket derived class with CSocketFile. After I run the application for 6 hours the memory increases in Task Manager by 30000K. The messages sent per second is 30.Has anybody seen such a issue. Regards Partha No, but there are so many problems with CSocket and CSocketFile that it is probably a Really Bad Idea to have used them at all, for any purpose. It sounds like you are not successfully closing some resource. But you would be a lot better off getting rid of them entirely. CSocket is known to be buggy, for example. joe On Sun, 6 Apr 2008 17:30:47 -0700 (PDT),...

Increasing field size in detail inquiry
I would like to increase the size of one of the fields in the account area of detail inquiry. Right now the W in account number W-123-4567-33-us looks like a V. Any help is appreciated. Thanks. -- Dave Christman System Developer Dave, You might have done this already but maybe not. Did you go to the Account Format Setup window and for your first account segment change the Display Width from "standard" to "Expansion 3"? That should do the trick for you. patrick developer support -- This posting is provided "AS IS" with no warranties, and confers no righ...

increasing arguments
I'm performing a simple addition calculation for material that is neste within other data, therefore i am unable to simpley sum the entir column........ Currently its telling me i have too many arguments......... =SUM(N146,N142,N138,N134,N130,N126,N122,N118,N114,N110,N106,N102,N98,N94,N90,N86,N82,N78,N74,N70,N66,N62,N58,N54,N50,N46,N42,N38,N34,N30,N26,N22) that is a total of 32 cells notated.......how can i increase th argument level from 30 to 32 or 33 ? Your assistance is greatl appreciated ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.c...

Increasing font size in replies
What would cause font sizes to increase each time an email is replied to? For example A sends email to B B replys to A and so on. The first email is now a huge font, the reply a little smaller and so on until the current email reached where it is a normal size. An email printed should only take 4-5 pages now takes about 30! RD Does this happen when a message travels over a Notes connector? >-----Original Message----- >What would cause font sizes to increase each time an email is replied to? > > For example A sends email to B B replys to A and so on. The first email is >...

Increase Potential
ACTING FOR THE NON ACTOR WITH AWARD WINNING ACTOR/WRITER/DIRECTOR/TEACHER MICHAEL COLE DINELLI www.gravitywavefilms.com Employ the tools of the Actor to enhance your communication creative skills in business, relationships, and everyday life. Learn the exercises and acting techniques the professionals use for their work in movies and stage. These classes are designed for the non-pro to be informative, great fun, and bring out the best in you weather you are a salesman, attorney, teacher, or just looking for something fun and therapeutic. Conquer your fear of public speaking and ...

Dual Processors and Macro Speed
Hi, I've just discovered this forum and I'm hoping that someone can hel me! I'm running a rather sizeable macro which generates a lot o calculations. I've already turned off screenupdating and optimised my code but it wa still taking many hours to run. I turned to new hardware to solve my problems........ I'm now running the macro on a dual 3.2ghz Xeon processor machine wit 4gb of RAM and XP professional. It's about 20% quicker than my last machine which had a single pentiu 4 processor and 1 gb ram, but not the 200% quicker I had been promise by our IT dept :( ...

Percentage increase in excel
Hi, I have a column showing profit over one year and i would like to calculate the percentage increase each month. For example my columns might be as follows. Jan - �3000 Feb - �4500 Mar - �7000 Apr - �9000 etc. So i need to calculate the percentage increase from Jan to Feb and so on. Does anybody know the formula? Thanks -- fibregrid ------------------------------------------------------------------------ fibregrid's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25707 View this thread: http://www.excelforum.com/showthread.php?threadid=401911 fibregri...

Increase Report Options in Report Group Window
Currently under the Reports - Financial - Groups window there are only 32 reports in the Report Options box. We have more than 32 reports set up, we would like to see all of the reports in order to put them into a group. We only need to add 10-15 reports into the group, but we need to see all of the reports we have set up. We would utilize the Group Reporting option more if we were able to see all of our reports. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I A...

Increase Max Value of Spinner
The Maximum Value of the "Spinner" form is 30,000. Is there any way to increase it? I'd like to bump the maximum value up into the millions. You can use the spinner button from the control toolbox instead -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you are in one stop digging." Lord Healey "Lon" <sobel@entertainmentlawreporter.com> wrote in message news:sdw0g.3416$oQ2.301@trnddc05... > The Maximum Value of the "Spinner"...

Increase of spam
Hello All, For quite a while I have managed to keep a lid on spam where that my peak of spam would be approximately 2 or 3 per day. There would be the rarity that I would receive 5 of them. Part of this is that I ensure to all my friends never to send me those "joke" e-mails where they automatically forward them to everyone that is in their address book into their "To:" box. This position has been for a number of years. You might can say that I am rather "authoritive" about this procedure. Putting all this aside, Microsoft Outlook 2007 has done a great...

Sumproduct formula works in spreadsheet, NOT VBA. Help please
Basically, I want to replace this formula with VBA Code I can get this one to work to sum =SUMPRODUCT(--(DataTime="First day of employment (Time 1)"),--(DataPosition=N6),(DataQuestion1) When I change this to Count rather than Sum, my Code Evaluates as an Error =SUMPRODUCT(--(DataTime="First day of employment (Time 1)"),--(DataPosition="Registered Nurse"),--(DataQuestion1<>"*")) The only line of code I switch out is the last mFormula row. The line of code that works I've commented out. I am really stumped... This is the cod...

Want to Increase Prices by Percent
New to this. Am using Office-Excel 2000. Have entered three columns of prices for company (List, Dealer, Distributor). Prices will increase next month. How do I increase all rows (example C3-C50) by 10 percent? Will need to do this for all three columns. -- ShirleyF one way 1.. In an empty cell, enter the number that you want to multiply by..... 1.10 2.. Select that cell, and on the Edit menu, click Copy. 3.. Select the whole range of numbers that you want to multiply. 4.. On the Edit menu, click Paste Special. 5.. Under Operation, click Multiply. 6.. Click OK. 7.. Delete th...

Increase in traffic
Hello A friend has exchange 2000 installed on a Windows 2000 server. All of a sudden there is a huge increase in network traffic and 50% of the users could access emails and the other 50% could not. Then after a while the roles were reversed, and the other 50% could access emails and the remaining 50% could not!!! Apparently there is 1GB of memory installed. I wondered if they had been hit with a virus. Last week they had a few problems with exchange as they no one could access emails. Temporarily the Mcafee Groupshield anitvirus had to be disabled to rectify the issues. The queues ha...

SUMPRODUCT with criteria including OR function
Hi chaps, Excel 2003 I'm using the following formula to sum the number of times that the #3 appears on the same Row in both Columns G3:G1002 & H3:H1002, where the value in Column AJ3:AJ1002 is between 2 other distinct values (located in a seperate table, cells I1320 & J1320). =IF(SUMPRODUCT(($AJ$3:$AJ$1002>=I1320)*($AJ$3:$AJ$1002<J1320)*($G$3:$G$1002=3)*($H$3:$H$1002=3))=0,"",SUMPRODUCT(($AJ$3:$AJ$1002>=I1320)*($AJ$3:$AJ$1002<J1320)*($G$3:$G$1002=3)*($H$3:$H$1002=3))) All works fine, but now instead of the #3 I need to introduce an OR function ...

How can I increase the character capcity of an Excel cell?
The current maximum is about 1,100. Excel can hold 32k characters of text and around 1000 in a formula. You can't change this fact of life. -- Jim "Steve" <Steve@discussions.microsoft.com> wrote in message news:442F16AB-1C7A-45CD-9A6F-E2C8282A2CDC@microsoft.com... | The current maximum is about 1,100. You can increase the ability of the cell to *display* more characters (cell can *contain* approx. 32,000) by strategically inserting forced line breaks (<Alt> <Enter>) within the text. -- HTH, RD ============================================== Please kee...

Is there a way to increase the storage space for Rules?
We have run into a problem with Exchange 2003 where it keeps giving a user a message that "One or more rules could not be uploaded to Exchange Server and have been deactivated." The user states that if he deletes a rule then checks this rule it works. So it doesn't appear to be a problem with the rule and he wants to increase the storage of the rules for him. How or can we even do that? Thanks, Jeff It is a hard coded limit... "Jeff" <jdiaz@p1fs.com> wrote in message news:bjW4e.31868$Pc.23177@tornado.tampabay.rr.com... > We have run into a proble...

How do I set up a Speed and directional graph
I want to have a graph that has 8 starting points across the bottom. They differ in the direction and the distance they go in a set period of time. I would like to show the intersecting lines if there is intersection between any of the points. This is a chart of 8 greyhounds starting from the starting box and running to the first turn. Some will run inside, some outside, and some in the middle of the chart and in 7 seconds they will run a different distance. Hence the intersection of lines or trouble points. ...