+ Reply to Thread
Results 1 to 45 of 45

Sumproduct Formula

Hybrid View

  1. #1
    Mike
    Guest

    Sumproduct Formula

    I have a workbook that uses over 3000 sumproduct formulas to extract
    data based on 7 different criteria. Works great except for calculation
    time.

    Is there an alternative to Sumproduct that would be faster and still
    allow for sumation based on multiple criteria?

    Is it possible to have Excel calculate certain cell ranges but not the
    entire workbook?

    Would additional RAM help? (I currently have 1 MB)

    Is there a way to identify things that make calc time longer?

    Thanks for any suggestions...


  2. #2
    Aladin Akyurek
    Guest

    Re: Sumproduct Formula

    One way is to reduce the number of ranges tested...

    Example

    Suppose we have

    [A]

    =SUMPRODUCT(($A$2:$A$1000=$K2)+0,($B$2:$B$1000=$L2)+0,$C$2:$C$1000)

    and we want to reduce the number of ranges tested:

    D2, copied down:

    =A2&"#"&B2

    The formula in [A] can be re-expressed as:

    [B]

    =SUMIF(($D$2:$D$1000,$K2&"#"&$L2,$C$2:$C$1000)

    For more ideas, see:

    http://tinyurl.com/d9eom

    For computing on relevant subranges instead of whole range, the
    following example might be helpful:

    http://tinyurl.com/cqy47

    And for more, see:

    http://tinyurl.com/d9eom

    Mike wrote:
    > I have a workbook that uses over 3000 sumproduct formulas to extract
    > data based on 7 different criteria. Works great except for calculation
    > time.
    >
    > Is there an alternative to Sumproduct that would be faster and still
    > allow for sumation based on multiple criteria?
    >
    > Is it possible to have Excel calculate certain cell ranges but not the
    > entire workbook?
    >
    > Would additional RAM help? (I currently have 1 MB)
    >
    > Is there a way to identify things that make calc time longer?
    >
    > Thanks for any suggestions...
    >


    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.

  3. #3
    RagDyer
    Guest

    Re: Sumproduct Formula

    Aladin,
    Would you comment on the perceived advantage of using
    +0
    Instead of the asterisk or the unary.
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------

    "Aladin Akyurek" <akyurek@xs4all.nl> wrote in message
    news:42fcfad1$0$11076$e4fe514c@news.xs4all.nl...
    > One way is to reduce the number of ranges tested...
    >
    > Example
    >
    > Suppose we have
    >
    > [A]
    >
    > =SUMPRODUCT(($A$2:$A$1000=$K2)+0,($B$2:$B$1000=$L2)+0,$C$2:$C$1000)
    >
    > and we want to reduce the number of ranges tested:
    >
    > D2, copied down:
    >
    > =A2&"#"&B2
    >
    > The formula in [A] can be re-expressed as:
    >
    > [B]
    >
    > =SUMIF(($D$2:$D$1000,$K2&"#"&$L2,$C$2:$C$1000)
    >
    > For more ideas, see:
    >
    > http://tinyurl.com/d9eom
    >
    > For computing on relevant subranges instead of whole range, the
    > following example might be helpful:
    >
    > http://tinyurl.com/cqy47
    >
    > And for more, see:
    >
    > http://tinyurl.com/d9eom
    >
    > Mike wrote:
    > > I have a workbook that uses over 3000 sumproduct formulas to extract
    > > data based on 7 different criteria. Works great except for calculation
    > > time.
    > >
    > > Is there an alternative to Sumproduct that would be faster and still
    > > allow for sumation based on multiple criteria?
    > >
    > > Is it possible to have Excel calculate certain cell ranges but not the
    > > entire workbook?
    > >
    > > Would additional RAM help? (I currently have 1 MB)
    > >
    > > Is there a way to identify things that make calc time longer?
    > >
    > > Thanks for any suggestions...
    > >

    >
    > --
    >
    > [1] The SumProduct function should implicitly coerce the truth values to
    > their Excel numeric equivalents.
    > [2] The lookup functions should have an optional argument for the return
    > value, defaulting to #N/A in its absence.



  4. #4
    RagDyer
    Guest

    Re: Sumproduct Formula

    Aladin,
    Would you comment on the perceived advantage of using
    +0
    Instead of the asterisk or the unary.
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------

    "Aladin Akyurek" <akyurek@xs4all.nl> wrote in message
    news:42fcfad1$0$11076$e4fe514c@news.xs4all.nl...
    > One way is to reduce the number of ranges tested...
    >
    > Example
    >
    > Suppose we have
    >
    > [A]
    >
    > =SUMPRODUCT(($A$2:$A$1000=$K2)+0,($B$2:$B$1000=$L2)+0,$C$2:$C$1000)
    >
    > and we want to reduce the number of ranges tested:
    >
    > D2, copied down:
    >
    > =A2&"#"&B2
    >
    > The formula in [A] can be re-expressed as:
    >
    > [B]
    >
    > =SUMIF(($D$2:$D$1000,$K2&"#"&$L2,$C$2:$C$1000)
    >
    > For more ideas, see:
    >
    > http://tinyurl.com/d9eom
    >
    > For computing on relevant subranges instead of whole range, the
    > following example might be helpful:
    >
    > http://tinyurl.com/cqy47
    >
    > And for more, see:
    >
    > http://tinyurl.com/d9eom
    >
    > Mike wrote:
    > > I have a workbook that uses over 3000 sumproduct formulas to extract
    > > data based on 7 different criteria. Works great except for calculation
    > > time.
    > >
    > > Is there an alternative to Sumproduct that would be faster and still
    > > allow for sumation based on multiple criteria?
    > >
    > > Is it possible to have Excel calculate certain cell ranges but not the
    > > entire workbook?
    > >
    > > Would additional RAM help? (I currently have 1 MB)
    > >
    > > Is there a way to identify things that make calc time longer?
    > >
    > > Thanks for any suggestions...
    > >

    >
    > --
    >
    > [1] The SumProduct function should implicitly coerce the truth values to
    > their Excel numeric equivalents.
    > [2] The lookup functions should have an optional argument for the return
    > value, defaulting to #N/A in its absence.



  5. #5
    RagDyer
    Guest

    Re: Sumproduct Formula

    Aladin,
    Would you comment on the perceived advantage of using
    +0
    Instead of the asterisk or the unary.
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------

    "Aladin Akyurek" <akyurek@xs4all.nl> wrote in message
    news:42fcfad1$0$11076$e4fe514c@news.xs4all.nl...
    > One way is to reduce the number of ranges tested...
    >
    > Example
    >
    > Suppose we have
    >
    > [A]
    >
    > =SUMPRODUCT(($A$2:$A$1000=$K2)+0,($B$2:$B$1000=$L2)+0,$C$2:$C$1000)
    >
    > and we want to reduce the number of ranges tested:
    >
    > D2, copied down:
    >
    > =A2&"#"&B2
    >
    > The formula in [A] can be re-expressed as:
    >
    > [B]
    >
    > =SUMIF(($D$2:$D$1000,$K2&"#"&$L2,$C$2:$C$1000)
    >
    > For more ideas, see:
    >
    > http://tinyurl.com/d9eom
    >
    > For computing on relevant subranges instead of whole range, the
    > following example might be helpful:
    >
    > http://tinyurl.com/cqy47
    >
    > And for more, see:
    >
    > http://tinyurl.com/d9eom
    >
    > Mike wrote:
    > > I have a workbook that uses over 3000 sumproduct formulas to extract
    > > data based on 7 different criteria. Works great except for calculation
    > > time.
    > >
    > > Is there an alternative to Sumproduct that would be faster and still
    > > allow for sumation based on multiple criteria?
    > >
    > > Is it possible to have Excel calculate certain cell ranges but not the
    > > entire workbook?
    > >
    > > Would additional RAM help? (I currently have 1 MB)
    > >
    > > Is there a way to identify things that make calc time longer?
    > >
    > > Thanks for any suggestions...
    > >

    >
    > --
    >
    > [1] The SumProduct function should implicitly coerce the truth values to
    > their Excel numeric equivalents.
    > [2] The lookup functions should have an optional argument for the return
    > value, defaulting to #N/A in its absence.



  6. #6
    Alan
    Guest

    Re: Sumproduct Formula

    I don't really think so. It depends on how many rows and columns your
    SUMPRODUCT's are looking at, but even with short row and column ranges, that
    many SUMPRODUCT formulas are going to take forever to calculate. Any formula
    based alternative, no matter how innovative would still be volatile and
    therefore would still be very slow to complete calculation,
    One possible way round it is to insert the formulas into the appropriate
    places via a Macro and then in the same Macro copy and Paste Special Values
    so that the file contains no formulas at all until the Macro(s) are run, and
    indeed, no formulas after the Macro(s) are run.
    You may perhaps be able to enter only the formulas that need to be
    calculated in a certain circumstance rather than all of them at the same
    time. This would speed up the calculation time significantly.
    Obviously without being able to see your sheet its difficult to say if the
    afore mentioned procedure would be feasible,
    Regards,
    Alan.

    "Mike" <mac159753@gmail.com> wrote in message
    news:1123870558.742057.51860@f14g2000cwb.googlegroups.com...
    >I have a workbook that uses over 3000 sumproduct formulas to extract
    > data based on 7 different criteria. Works great except for calculation
    > time.
    >
    > Is there an alternative to Sumproduct that would be faster and still
    > allow for sumation based on multiple criteria?
    >
    > Is it possible to have Excel calculate certain cell ranges but not the
    > entire workbook?
    >
    > Would additional RAM help? (I currently have 1 MB)
    >
    > Is there a way to identify things that make calc time longer?
    >
    > Thanks for any suggestions...
    >




  7. #7
    Ashish Mathur
    Guest

    RE: Sumproduct Formula

    Hi Mike,

    An effectivre alternative to using the sumproduct formula could be the
    Database functions of Excel i.e. functions like DSUM, DGET, DPRODUCT etc.
    You will get extensive help in the HELP Menu.

    Hope this information helps.

    Regards,

    "Mike" wrote:

    > I have a workbook that uses over 3000 sumproduct formulas to extract
    > data based on 7 different criteria. Works great except for calculation
    > time.
    >
    > Is there an alternative to Sumproduct that would be faster and still
    > allow for sumation based on multiple criteria?
    >
    > Is it possible to have Excel calculate certain cell ranges but not the
    > entire workbook?
    >
    > Would additional RAM help? (I currently have 1 MB)
    >
    > Is there a way to identify things that make calc time longer?
    >
    > Thanks for any suggestions...
    >
    >


  8. #8
    Ashish Mathur
    Guest

    RE: Sumproduct Formula

    Hi Mike,

    An effectivre alternative to using the sumproduct formula could be the
    Database functions of Excel i.e. functions like DSUM, DGET, DPRODUCT etc.
    You will get extensive help in the HELP Menu.

    Hope this information helps.

    Regards,

    "Mike" wrote:

    > I have a workbook that uses over 3000 sumproduct formulas to extract
    > data based on 7 different criteria. Works great except for calculation
    > time.
    >
    > Is there an alternative to Sumproduct that would be faster and still
    > allow for sumation based on multiple criteria?
    >
    > Is it possible to have Excel calculate certain cell ranges but not the
    > entire workbook?
    >
    > Would additional RAM help? (I currently have 1 MB)
    >
    > Is there a way to identify things that make calc time longer?
    >
    > Thanks for any suggestions...
    >
    >


  9. #9
    Ashish Mathur
    Guest

    RE: Sumproduct Formula

    Hi Mike,

    An effectivre alternative to using the sumproduct formula could be the
    Database functions of Excel i.e. functions like DSUM, DGET, DPRODUCT etc.
    You will get extensive help in the HELP Menu.

    Hope this information helps.

    Regards,

    "Mike" wrote:

    > I have a workbook that uses over 3000 sumproduct formulas to extract
    > data based on 7 different criteria. Works great except for calculation
    > time.
    >
    > Is there an alternative to Sumproduct that would be faster and still
    > allow for sumation based on multiple criteria?
    >
    > Is it possible to have Excel calculate certain cell ranges but not the
    > entire workbook?
    >
    > Would additional RAM help? (I currently have 1 MB)
    >
    > Is there a way to identify things that make calc time longer?
    >
    > Thanks for any suggestions...
    >
    >


  10. #10
    David Billigmeier
    Guest

    RE: Sumproduct Formula

    Have you tried using pivot tables? They are extremely quick and summarize
    data similarly as SUMPRODUCT does.
    --
    Regards,

    David Billigmeier



    "Mike" wrote:

    > I have a workbook that uses over 3000 sumproduct formulas to extract
    > data based on 7 different criteria. Works great except for calculation
    > time.
    >
    > Is there an alternative to Sumproduct that would be faster and still
    > allow for sumation based on multiple criteria?
    >
    > Is it possible to have Excel calculate certain cell ranges but not the
    > entire workbook?
    >
    > Would additional RAM help? (I currently have 1 MB)
    >
    > Is there a way to identify things that make calc time longer?
    >
    > Thanks for any suggestions...
    >
    >


  11. #11
    Aladin Akyurek
    Guest

    Re: Sumproduct Formula

    One way is to reduce the number of ranges tested...

    Example

    Suppose we have

    [A]

    =SUMPRODUCT(($A$2:$A$1000=$K2)+0,($B$2:$B$1000=$L2)+0,$C$2:$C$1000)

    and we want to reduce the number of ranges tested:

    D2, copied down:

    =A2&"#"&B2

    The formula in [A] can be re-expressed as:

    [B]

    =SUMIF(($D$2:$D$1000,$K2&"#"&$L2,$C$2:$C$1000)

    For more ideas, see:

    http://tinyurl.com/d9eom

    For computing on relevant subranges instead of whole range, the
    following example might be helpful:

    http://tinyurl.com/cqy47

    And for more, see:

    http://tinyurl.com/d9eom

    Mike wrote:
    > I have a workbook that uses over 3000 sumproduct formulas to extract
    > data based on 7 different criteria. Works great except for calculation
    > time.
    >
    > Is there an alternative to Sumproduct that would be faster and still
    > allow for sumation based on multiple criteria?
    >
    > Is it possible to have Excel calculate certain cell ranges but not the
    > entire workbook?
    >
    > Would additional RAM help? (I currently have 1 MB)
    >
    > Is there a way to identify things that make calc time longer?
    >
    > Thanks for any suggestions...
    >


    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.

  12. #12
    RagDyer
    Guest

    Re: Sumproduct Formula

    Aladin,
    Would you comment on the perceived advantage of using
    +0
    Instead of the asterisk or the unary.
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------

    "Aladin Akyurek" <akyurek@xs4all.nl> wrote in message
    news:42fcfad1$0$11076$e4fe514c@news.xs4all.nl...
    > One way is to reduce the number of ranges tested...
    >
    > Example
    >
    > Suppose we have
    >
    > [A]
    >
    > =SUMPRODUCT(($A$2:$A$1000=$K2)+0,($B$2:$B$1000=$L2)+0,$C$2:$C$1000)
    >
    > and we want to reduce the number of ranges tested:
    >
    > D2, copied down:
    >
    > =A2&"#"&B2
    >
    > The formula in [A] can be re-expressed as:
    >
    > [B]
    >
    > =SUMIF(($D$2:$D$1000,$K2&"#"&$L2,$C$2:$C$1000)
    >
    > For more ideas, see:
    >
    > http://tinyurl.com/d9eom
    >
    > For computing on relevant subranges instead of whole range, the
    > following example might be helpful:
    >
    > http://tinyurl.com/cqy47
    >
    > And for more, see:
    >
    > http://tinyurl.com/d9eom
    >
    > Mike wrote:
    > > I have a workbook that uses over 3000 sumproduct formulas to extract
    > > data based on 7 different criteria. Works great except for calculation
    > > time.
    > >
    > > Is there an alternative to Sumproduct that would be faster and still
    > > allow for sumation based on multiple criteria?
    > >
    > > Is it possible to have Excel calculate certain cell ranges but not the
    > > entire workbook?
    > >
    > > Would additional RAM help? (I currently have 1 MB)
    > >
    > > Is there a way to identify things that make calc time longer?
    > >
    > > Thanks for any suggestions...
    > >

    >
    > --
    >
    > [1] The SumProduct function should implicitly coerce the truth values to
    > their Excel numeric equivalents.
    > [2] The lookup functions should have an optional argument for the return
    > value, defaulting to #N/A in its absence.



  13. #13
    Aladin Akyurek
    Guest

    Re: Sumproduct Formula

    Performancewise, +0 is very close to --, the double negation, both
    better than other coercers. Invoking one as the other is a "weak"
    attempt on my part to emphasize the real thing, that is, the comma
    syntax, which is confounded by far too many with the coercer issue. All
    this will subside when SumProduct does the coercion itself. See [1] in
    my signature, which is a SumProduct-specific proposal.

    RagDyer wrote:
    > Aladin,
    > Would you comment on the perceived advantage of using
    > +0
    > Instead of the asterisk or the unary.


    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.

  14. #14
    Aladin Akyurek
    Guest

    Re: Sumproduct Formula

    Performancewise, +0 is very close to --, the double negation, both
    better than other coercers. Invoking one as the other is a "weak"
    attempt on my part to emphasize the real thing, that is, the comma
    syntax, which is confounded by far too many with the coercer issue. All
    this will subside when SumProduct does the coercion itself. See [1] in
    my signature, which is a SumProduct-specific proposal.

    RagDyer wrote:
    > Aladin,
    > Would you comment on the perceived advantage of using
    > +0
    > Instead of the asterisk or the unary.


    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.

  15. #15
    Aladin Akyurek
    Guest

    Re: Sumproduct Formula

    Performancewise, +0 is very close to --, the double negation, both
    better than other coercers. Invoking one as the other is a "weak"
    attempt on my part to emphasize the real thing, that is, the comma
    syntax, which is confounded by far too many with the coercer issue. All
    this will subside when SumProduct does the coercion itself. See [1] in
    my signature, which is a SumProduct-specific proposal.

    RagDyer wrote:
    > Aladin,
    > Would you comment on the perceived advantage of using
    > +0
    > Instead of the asterisk or the unary.


    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.

  16. #16
    Aladin Akyurek
    Guest

    Re: Sumproduct Formula

    Performancewise, +0 is very close to --, the double negation, both
    better than other coercers. Invoking one as the other is a "weak"
    attempt on my part to emphasize the real thing, that is, the comma
    syntax, which is confounded by far too many with the coercer issue. All
    this will subside when SumProduct does the coercion itself. See [1] in
    my signature, which is a SumProduct-specific proposal.

    RagDyer wrote:
    > Aladin,
    > Would you comment on the perceived advantage of using
    > +0
    > Instead of the asterisk or the unary.


    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.

  17. #17
    RagDyeR
    Guest

    Re: Sumproduct Formula

    Thanks for your answer Aladin.

    This is the second time in as many days that I'm bringing up *my* issue with
    the unary, or as you mention here, the "comma" syntax.

    In today's world, the use of the web for mining data is as common a fact of
    life as is the use of the computer itself.
    They are in fact, virtually synonymous.

    Data imported into XL, and the form and format of this data, is a very
    common issue within these NGs.
    On a daily basis, there are numerous questions pertaining to unworkable
    formulas, where the ultimate solution is to "homogenize" the data forms and
    formats.

    The "problem" with the comma syntax is, it's *sneaky*!
    In a convoluted scenario of "mixed" data, it returns a "wrong" result,
    without any conspicuous declaration.
    Zero is calculated for the "bad" data (numeric text as well as alpha text),
    and its result is mixed in with the "good" data.

    The developer completes the project and it's turned over to office staff for
    implementation.
    Then, let the cards fall where they may!

    The asterisk form, on the other hand, *does* calculate the numeric text, and
    "errors out" in the presence of alpha text, thus performing double duty.
    It's the notification that's the important thing.
    If you're told something's wrong, you can look for it!

    There are cases where the comma form is necessary, but that's a mute point
    in this discourse.

    I'll get off my soap box now.<g>
    --

    Regards,

    RD
    ----------------------------------------------------------------------------
    -------------------
    Please keep all correspondence within the Group, so all may benefit !
    ----------------------------------------------------------------------------
    -------------------



    "Aladin Akyurek" <akyurek@xs4all.nl> wrote in message
    news:42fdd974$0$11064$e4fe514c@news.xs4all.nl...
    Performancewise, +0 is very close to --, the double negation, both
    better than other coercers. Invoking one as the other is a "weak"
    attempt on my part to emphasize the real thing, that is, the comma
    syntax, which is confounded by far too many with the coercer issue. All
    this will subside when SumProduct does the coercion itself. See [1] in
    my signature, which is a SumProduct-specific proposal.

    RagDyer wrote:
    > Aladin,
    > Would you comment on the perceived advantage of using
    > +0
    > Instead of the asterisk or the unary.


    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.



  18. #18
    Aladin Akyurek
    Guest

    Re: Sumproduct Formula


    RagDyeR wrote:

    [...]
    > This is the second time in as many days that I'm bringing up *my* issue with
    > the unary, or as you mention here, the "comma" syntax.


    To clear up any misunderstanding, I'm definitely in favor of the
    comma-syntax, while you are, in fact, arguing/recommending against the
    use of SumProduct's comma-syntax.

    >
    > In today's world, the use of the web for mining data is as common a fact of
    > life as is the use of the computer itself.
    > They are in fact, virtually synonymous.
    >


    I don't think so. That was the point of my reply.

    > Data imported into XL, and the form and format of this data, is a very
    > common issue within these NGs.
    > On a daily basis, there are numerous questions pertaining to unworkable
    > formulas, where the ultimate solution is to "homogenize" the data forms and
    > formats.


    Issues with numeric data, erroneously typed as text (either by user or
    by the "vagaries" of the system's parser) do not constitute a valid
    reason to delegate the re-solution to functions.

    >
    > The "problem" with the comma syntax is, it's *sneaky*!
    > In a convoluted scenario of "mixed" data, it returns a "wrong" result,
    > without any conspicuous declaration.
    > Zero is calculated for the "bad" data (numeric text as well as alpha text),
    > and its result is mixed in with the "good" data.
    >


    Such concerns are better dealt with by means of separate formulas that
    audit the data. If a range should be numeric, a simple audit formula can
    verify whether that is the case. For example:

    =COUNT(Range)=ROWS(Range)

    As a side note, I teach this subject in my audit classes with the 3rd
    year accountancy students.

    > The developer completes the project and it's turned over to office staff for
    > implementation.
    > Then, let the cards fall where they may!


    The developer should provide an audit sheet (rarely done), regarding the
    data types and the processing a spreadsheet model carries out. Auditors
    (e.g., accountants) ought to require audit sheets.

    >
    > The asterisk form, on the other hand, *does* calculate the numeric text, and
    > "errors out" in the presence of alpha text, thus performing double duty.
    > It's the notification that's the important thing.


    A data area can consist of either user-entered values or calculated
    values. There might be good reasons for using ="" or any other
    text-value. Such an area becomes unprocessible by your suggestion.

    > If you're told something's wrong, you can look for it!


    Quite so. One would be well-advised to inspect the results of
    judiciously set up audit formulas.

    [...]

    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.

  19. #19
    Aladin Akyurek
    Guest

    Re: Sumproduct Formula


    RagDyeR wrote:

    [...]
    > This is the second time in as many days that I'm bringing up *my* issue with
    > the unary, or as you mention here, the "comma" syntax.


    To clear up any misunderstanding, I'm definitely in favor of the
    comma-syntax, while you are, in fact, arguing/recommending against the
    use of SumProduct's comma-syntax.

    >
    > In today's world, the use of the web for mining data is as common a fact of
    > life as is the use of the computer itself.
    > They are in fact, virtually synonymous.
    >


    I don't think so. That was the point of my reply.

    > Data imported into XL, and the form and format of this data, is a very
    > common issue within these NGs.
    > On a daily basis, there are numerous questions pertaining to unworkable
    > formulas, where the ultimate solution is to "homogenize" the data forms and
    > formats.


    Issues with numeric data, erroneously typed as text (either by user or
    by the "vagaries" of the system's parser) do not constitute a valid
    reason to delegate the re-solution to functions.

    >
    > The "problem" with the comma syntax is, it's *sneaky*!
    > In a convoluted scenario of "mixed" data, it returns a "wrong" result,
    > without any conspicuous declaration.
    > Zero is calculated for the "bad" data (numeric text as well as alpha text),
    > and its result is mixed in with the "good" data.
    >


    Such concerns are better dealt with by means of separate formulas that
    audit the data. If a range should be numeric, a simple audit formula can
    verify whether that is the case. For example:

    =COUNT(Range)=ROWS(Range)

    As a side note, I teach this subject in my audit classes with the 3rd
    year accountancy students.

    > The developer completes the project and it's turned over to office staff for
    > implementation.
    > Then, let the cards fall where they may!


    The developer should provide an audit sheet (rarely done), regarding the
    data types and the processing a spreadsheet model carries out. Auditors
    (e.g., accountants) ought to require audit sheets.

    >
    > The asterisk form, on the other hand, *does* calculate the numeric text, and
    > "errors out" in the presence of alpha text, thus performing double duty.
    > It's the notification that's the important thing.


    A data area can consist of either user-entered values or calculated
    values. There might be good reasons for using ="" or any other
    text-value. Such an area becomes unprocessible by your suggestion.

    > If you're told something's wrong, you can look for it!


    Quite so. One would be well-advised to inspect the results of
    judiciously set up audit formulas.

    [...]

    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.

  20. #20
    Aladin Akyurek
    Guest

    Re: Sumproduct Formula


    RagDyeR wrote:

    [...]
    > This is the second time in as many days that I'm bringing up *my* issue with
    > the unary, or as you mention here, the "comma" syntax.


    To clear up any misunderstanding, I'm definitely in favor of the
    comma-syntax, while you are, in fact, arguing/recommending against the
    use of SumProduct's comma-syntax.

    >
    > In today's world, the use of the web for mining data is as common a fact of
    > life as is the use of the computer itself.
    > They are in fact, virtually synonymous.
    >


    I don't think so. That was the point of my reply.

    > Data imported into XL, and the form and format of this data, is a very
    > common issue within these NGs.
    > On a daily basis, there are numerous questions pertaining to unworkable
    > formulas, where the ultimate solution is to "homogenize" the data forms and
    > formats.


    Issues with numeric data, erroneously typed as text (either by user or
    by the "vagaries" of the system's parser) do not constitute a valid
    reason to delegate the re-solution to functions.

    >
    > The "problem" with the comma syntax is, it's *sneaky*!
    > In a convoluted scenario of "mixed" data, it returns a "wrong" result,
    > without any conspicuous declaration.
    > Zero is calculated for the "bad" data (numeric text as well as alpha text),
    > and its result is mixed in with the "good" data.
    >


    Such concerns are better dealt with by means of separate formulas that
    audit the data. If a range should be numeric, a simple audit formula can
    verify whether that is the case. For example:

    =COUNT(Range)=ROWS(Range)

    As a side note, I teach this subject in my audit classes with the 3rd
    year accountancy students.

    > The developer completes the project and it's turned over to office staff for
    > implementation.
    > Then, let the cards fall where they may!


    The developer should provide an audit sheet (rarely done), regarding the
    data types and the processing a spreadsheet model carries out. Auditors
    (e.g., accountants) ought to require audit sheets.

    >
    > The asterisk form, on the other hand, *does* calculate the numeric text, and
    > "errors out" in the presence of alpha text, thus performing double duty.
    > It's the notification that's the important thing.


    A data area can consist of either user-entered values or calculated
    values. There might be good reasons for using ="" or any other
    text-value. Such an area becomes unprocessible by your suggestion.

    > If you're told something's wrong, you can look for it!


    Quite so. One would be well-advised to inspect the results of
    judiciously set up audit formulas.

    [...]

    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.

  21. #21
    RagDyeR
    Guest

    Re: Sumproduct Formula

    Thanks for your answer Aladin.

    This is the second time in as many days that I'm bringing up *my* issue with
    the unary, or as you mention here, the "comma" syntax.

    In today's world, the use of the web for mining data is as common a fact of
    life as is the use of the computer itself.
    They are in fact, virtually synonymous.

    Data imported into XL, and the form and format of this data, is a very
    common issue within these NGs.
    On a daily basis, there are numerous questions pertaining to unworkable
    formulas, where the ultimate solution is to "homogenize" the data forms and
    formats.

    The "problem" with the comma syntax is, it's *sneaky*!
    In a convoluted scenario of "mixed" data, it returns a "wrong" result,
    without any conspicuous declaration.
    Zero is calculated for the "bad" data (numeric text as well as alpha text),
    and its result is mixed in with the "good" data.

    The developer completes the project and it's turned over to office staff for
    implementation.
    Then, let the cards fall where they may!

    The asterisk form, on the other hand, *does* calculate the numeric text, and
    "errors out" in the presence of alpha text, thus performing double duty.
    It's the notification that's the important thing.
    If you're told something's wrong, you can look for it!

    There are cases where the comma form is necessary, but that's a mute point
    in this discourse.

    I'll get off my soap box now.<g>
    --

    Regards,

    RD
    ----------------------------------------------------------------------------
    -------------------
    Please keep all correspondence within the Group, so all may benefit !
    ----------------------------------------------------------------------------
    -------------------



    "Aladin Akyurek" <akyurek@xs4all.nl> wrote in message
    news:42fdd974$0$11064$e4fe514c@news.xs4all.nl...
    Performancewise, +0 is very close to --, the double negation, both
    better than other coercers. Invoking one as the other is a "weak"
    attempt on my part to emphasize the real thing, that is, the comma
    syntax, which is confounded by far too many with the coercer issue. All
    this will subside when SumProduct does the coercion itself. See [1] in
    my signature, which is a SumProduct-specific proposal.

    RagDyer wrote:
    > Aladin,
    > Would you comment on the perceived advantage of using
    > +0
    > Instead of the asterisk or the unary.


    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.



  22. #22
    RagDyeR
    Guest

    Re: Sumproduct Formula

    Thanks for your answer Aladin.

    This is the second time in as many days that I'm bringing up *my* issue with
    the unary, or as you mention here, the "comma" syntax.

    In today's world, the use of the web for mining data is as common a fact of
    life as is the use of the computer itself.
    They are in fact, virtually synonymous.

    Data imported into XL, and the form and format of this data, is a very
    common issue within these NGs.
    On a daily basis, there are numerous questions pertaining to unworkable
    formulas, where the ultimate solution is to "homogenize" the data forms and
    formats.

    The "problem" with the comma syntax is, it's *sneaky*!
    In a convoluted scenario of "mixed" data, it returns a "wrong" result,
    without any conspicuous declaration.
    Zero is calculated for the "bad" data (numeric text as well as alpha text),
    and its result is mixed in with the "good" data.

    The developer completes the project and it's turned over to office staff for
    implementation.
    Then, let the cards fall where they may!

    The asterisk form, on the other hand, *does* calculate the numeric text, and
    "errors out" in the presence of alpha text, thus performing double duty.
    It's the notification that's the important thing.
    If you're told something's wrong, you can look for it!

    There are cases where the comma form is necessary, but that's a mute point
    in this discourse.

    I'll get off my soap box now.<g>
    --

    Regards,

    RD
    ----------------------------------------------------------------------------
    -------------------
    Please keep all correspondence within the Group, so all may benefit !
    ----------------------------------------------------------------------------
    -------------------



    "Aladin Akyurek" <akyurek@xs4all.nl> wrote in message
    news:42fdd974$0$11064$e4fe514c@news.xs4all.nl...
    Performancewise, +0 is very close to --, the double negation, both
    better than other coercers. Invoking one as the other is a "weak"
    attempt on my part to emphasize the real thing, that is, the comma
    syntax, which is confounded by far too many with the coercer issue. All
    this will subside when SumProduct does the coercion itself. See [1] in
    my signature, which is a SumProduct-specific proposal.

    RagDyer wrote:
    > Aladin,
    > Would you comment on the perceived advantage of using
    > +0
    > Instead of the asterisk or the unary.


    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.



  23. #23
    RagDyeR
    Guest

    Re: Sumproduct Formula

    Thanks for your answer Aladin.

    This is the second time in as many days that I'm bringing up *my* issue with
    the unary, or as you mention here, the "comma" syntax.

    In today's world, the use of the web for mining data is as common a fact of
    life as is the use of the computer itself.
    They are in fact, virtually synonymous.

    Data imported into XL, and the form and format of this data, is a very
    common issue within these NGs.
    On a daily basis, there are numerous questions pertaining to unworkable
    formulas, where the ultimate solution is to "homogenize" the data forms and
    formats.

    The "problem" with the comma syntax is, it's *sneaky*!
    In a convoluted scenario of "mixed" data, it returns a "wrong" result,
    without any conspicuous declaration.
    Zero is calculated for the "bad" data (numeric text as well as alpha text),
    and its result is mixed in with the "good" data.

    The developer completes the project and it's turned over to office staff for
    implementation.
    Then, let the cards fall where they may!

    The asterisk form, on the other hand, *does* calculate the numeric text, and
    "errors out" in the presence of alpha text, thus performing double duty.
    It's the notification that's the important thing.
    If you're told something's wrong, you can look for it!

    There are cases where the comma form is necessary, but that's a mute point
    in this discourse.

    I'll get off my soap box now.<g>
    --

    Regards,

    RD
    ----------------------------------------------------------------------------
    -------------------
    Please keep all correspondence within the Group, so all may benefit !
    ----------------------------------------------------------------------------
    -------------------



    "Aladin Akyurek" <akyurek@xs4all.nl> wrote in message
    news:42fdd974$0$11064$e4fe514c@news.xs4all.nl...
    Performancewise, +0 is very close to --, the double negation, both
    better than other coercers. Invoking one as the other is a "weak"
    attempt on my part to emphasize the real thing, that is, the comma
    syntax, which is confounded by far too many with the coercer issue. All
    this will subside when SumProduct does the coercion itself. See [1] in
    my signature, which is a SumProduct-specific proposal.

    RagDyer wrote:
    > Aladin,
    > Would you comment on the perceived advantage of using
    > +0
    > Instead of the asterisk or the unary.


    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.



  24. #24
    Aladin Akyurek
    Guest

    Re: Sumproduct Formula


    RagDyeR wrote:

    [...]
    > This is the second time in as many days that I'm bringing up *my* issue with
    > the unary, or as you mention here, the "comma" syntax.


    To clear up any misunderstanding, I'm definitely in favor of the
    comma-syntax, while you are, in fact, arguing/recommending against the
    use of SumProduct's comma-syntax.

    >
    > In today's world, the use of the web for mining data is as common a fact of
    > life as is the use of the computer itself.
    > They are in fact, virtually synonymous.
    >


    I don't think so. That was the point of my reply.

    > Data imported into XL, and the form and format of this data, is a very
    > common issue within these NGs.
    > On a daily basis, there are numerous questions pertaining to unworkable
    > formulas, where the ultimate solution is to "homogenize" the data forms and
    > formats.


    Issues with numeric data, erroneously typed as text (either by user or
    by the "vagaries" of the system's parser) do not constitute a valid
    reason to delegate the re-solution to functions.

    >
    > The "problem" with the comma syntax is, it's *sneaky*!
    > In a convoluted scenario of "mixed" data, it returns a "wrong" result,
    > without any conspicuous declaration.
    > Zero is calculated for the "bad" data (numeric text as well as alpha text),
    > and its result is mixed in with the "good" data.
    >


    Such concerns are better dealt with by means of separate formulas that
    audit the data. If a range should be numeric, a simple audit formula can
    verify whether that is the case. For example:

    =COUNT(Range)=ROWS(Range)

    As a side note, I teach this subject in my audit classes with the 3rd
    year accountancy students.

    > The developer completes the project and it's turned over to office staff for
    > implementation.
    > Then, let the cards fall where they may!


    The developer should provide an audit sheet (rarely done), regarding the
    data types and the processing a spreadsheet model carries out. Auditors
    (e.g., accountants) ought to require audit sheets.

    >
    > The asterisk form, on the other hand, *does* calculate the numeric text, and
    > "errors out" in the presence of alpha text, thus performing double duty.
    > It's the notification that's the important thing.


    A data area can consist of either user-entered values or calculated
    values. There might be good reasons for using ="" or any other
    text-value. Such an area becomes unprocessible by your suggestion.

    > If you're told something's wrong, you can look for it!


    Quite so. One would be well-advised to inspect the results of
    judiciously set up audit formulas.

    [...]

    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.

  25. #25
    RagDyer
    Guest

    Re: Sumproduct Formula

    Aladin,
    Would you comment on the perceived advantage of using
    +0
    Instead of the asterisk or the unary.
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------

    "Aladin Akyurek" <akyurek@xs4all.nl> wrote in message
    news:42fcfad1$0$11076$e4fe514c@news.xs4all.nl...
    > One way is to reduce the number of ranges tested...
    >
    > Example
    >
    > Suppose we have
    >
    > [A]
    >
    > =SUMPRODUCT(($A$2:$A$1000=$K2)+0,($B$2:$B$1000=$L2)+0,$C$2:$C$1000)
    >
    > and we want to reduce the number of ranges tested:
    >
    > D2, copied down:
    >
    > =A2&"#"&B2
    >
    > The formula in [A] can be re-expressed as:
    >
    > [B]
    >
    > =SUMIF(($D$2:$D$1000,$K2&"#"&$L2,$C$2:$C$1000)
    >
    > For more ideas, see:
    >
    > http://tinyurl.com/d9eom
    >
    > For computing on relevant subranges instead of whole range, the
    > following example might be helpful:
    >
    > http://tinyurl.com/cqy47
    >
    > And for more, see:
    >
    > http://tinyurl.com/d9eom
    >
    > Mike wrote:
    > > I have a workbook that uses over 3000 sumproduct formulas to extract
    > > data based on 7 different criteria. Works great except for calculation
    > > time.
    > >
    > > Is there an alternative to Sumproduct that would be faster and still
    > > allow for sumation based on multiple criteria?
    > >
    > > Is it possible to have Excel calculate certain cell ranges but not the
    > > entire workbook?
    > >
    > > Would additional RAM help? (I currently have 1 MB)
    > >
    > > Is there a way to identify things that make calc time longer?
    > >
    > > Thanks for any suggestions...
    > >

    >
    > --
    >
    > [1] The SumProduct function should implicitly coerce the truth values to
    > their Excel numeric equivalents.
    > [2] The lookup functions should have an optional argument for the return
    > value, defaulting to #N/A in its absence.



  26. #26
    Aladin Akyurek
    Guest

    Re: Sumproduct Formula

    Performancewise, +0 is very close to --, the double negation, both
    better than other coercers. Invoking one as the other is a "weak"
    attempt on my part to emphasize the real thing, that is, the comma
    syntax, which is confounded by far too many with the coercer issue. All
    this will subside when SumProduct does the coercion itself. See [1] in
    my signature, which is a SumProduct-specific proposal.

    RagDyer wrote:
    > Aladin,
    > Would you comment on the perceived advantage of using
    > +0
    > Instead of the asterisk or the unary.


    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.

  27. #27
    RagDyeR
    Guest

    Re: Sumproduct Formula

    Thanks for your answer Aladin.

    This is the second time in as many days that I'm bringing up *my* issue with
    the unary, or as you mention here, the "comma" syntax.

    In today's world, the use of the web for mining data is as common a fact of
    life as is the use of the computer itself.
    They are in fact, virtually synonymous.

    Data imported into XL, and the form and format of this data, is a very
    common issue within these NGs.
    On a daily basis, there are numerous questions pertaining to unworkable
    formulas, where the ultimate solution is to "homogenize" the data forms and
    formats.

    The "problem" with the comma syntax is, it's *sneaky*!
    In a convoluted scenario of "mixed" data, it returns a "wrong" result,
    without any conspicuous declaration.
    Zero is calculated for the "bad" data (numeric text as well as alpha text),
    and its result is mixed in with the "good" data.

    The developer completes the project and it's turned over to office staff for
    implementation.
    Then, let the cards fall where they may!

    The asterisk form, on the other hand, *does* calculate the numeric text, and
    "errors out" in the presence of alpha text, thus performing double duty.
    It's the notification that's the important thing.
    If you're told something's wrong, you can look for it!

    There are cases where the comma form is necessary, but that's a mute point
    in this discourse.

    I'll get off my soap box now.<g>
    --

    Regards,

    RD
    ----------------------------------------------------------------------------
    -------------------
    Please keep all correspondence within the Group, so all may benefit !
    ----------------------------------------------------------------------------
    -------------------



    "Aladin Akyurek" <akyurek@xs4all.nl> wrote in message
    news:42fdd974$0$11064$e4fe514c@news.xs4all.nl...
    Performancewise, +0 is very close to --, the double negation, both
    better than other coercers. Invoking one as the other is a "weak"
    attempt on my part to emphasize the real thing, that is, the comma
    syntax, which is confounded by far too many with the coercer issue. All
    this will subside when SumProduct does the coercion itself. See [1] in
    my signature, which is a SumProduct-specific proposal.

    RagDyer wrote:
    > Aladin,
    > Would you comment on the perceived advantage of using
    > +0
    > Instead of the asterisk or the unary.


    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.



  28. #28
    Aladin Akyurek
    Guest

    Re: Sumproduct Formula


    RagDyeR wrote:

    [...]
    > This is the second time in as many days that I'm bringing up *my* issue with
    > the unary, or as you mention here, the "comma" syntax.


    To clear up any misunderstanding, I'm definitely in favor of the
    comma-syntax, while you are, in fact, arguing/recommending against the
    use of SumProduct's comma-syntax.

    >
    > In today's world, the use of the web for mining data is as common a fact of
    > life as is the use of the computer itself.
    > They are in fact, virtually synonymous.
    >


    I don't think so. That was the point of my reply.

    > Data imported into XL, and the form and format of this data, is a very
    > common issue within these NGs.
    > On a daily basis, there are numerous questions pertaining to unworkable
    > formulas, where the ultimate solution is to "homogenize" the data forms and
    > formats.


    Issues with numeric data, erroneously typed as text (either by user or
    by the "vagaries" of the system's parser) do not constitute a valid
    reason to delegate the re-solution to functions.

    >
    > The "problem" with the comma syntax is, it's *sneaky*!
    > In a convoluted scenario of "mixed" data, it returns a "wrong" result,
    > without any conspicuous declaration.
    > Zero is calculated for the "bad" data (numeric text as well as alpha text),
    > and its result is mixed in with the "good" data.
    >


    Such concerns are better dealt with by means of separate formulas that
    audit the data. If a range should be numeric, a simple audit formula can
    verify whether that is the case. For example:

    =COUNT(Range)=ROWS(Range)

    As a side note, I teach this subject in my audit classes with the 3rd
    year accountancy students.

    > The developer completes the project and it's turned over to office staff for
    > implementation.
    > Then, let the cards fall where they may!


    The developer should provide an audit sheet (rarely done), regarding the
    data types and the processing a spreadsheet model carries out. Auditors
    (e.g., accountants) ought to require audit sheets.

    >
    > The asterisk form, on the other hand, *does* calculate the numeric text, and
    > "errors out" in the presence of alpha text, thus performing double duty.
    > It's the notification that's the important thing.


    A data area can consist of either user-entered values or calculated
    values. There might be good reasons for using ="" or any other
    text-value. Such an area becomes unprocessible by your suggestion.

    > If you're told something's wrong, you can look for it!


    Quite so. One would be well-advised to inspect the results of
    judiciously set up audit formulas.

    [...]

    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.

  29. #29
    Ashish Mathur
    Guest

    RE: Sumproduct Formula

    Hi Mike,

    An effectivre alternative to using the sumproduct formula could be the
    Database functions of Excel i.e. functions like DSUM, DGET, DPRODUCT etc.
    You will get extensive help in the HELP Menu.

    Hope this information helps.

    Regards,

    "Mike" wrote:

    > I have a workbook that uses over 3000 sumproduct formulas to extract
    > data based on 7 different criteria. Works great except for calculation
    > time.
    >
    > Is there an alternative to Sumproduct that would be faster and still
    > allow for sumation based on multiple criteria?
    >
    > Is it possible to have Excel calculate certain cell ranges but not the
    > entire workbook?
    >
    > Would additional RAM help? (I currently have 1 MB)
    >
    > Is there a way to identify things that make calc time longer?
    >
    > Thanks for any suggestions...
    >
    >


  30. #30
    Ashish Mathur
    Guest

    RE: Sumproduct Formula

    Hi Mike,

    An effectivre alternative to using the sumproduct formula could be the
    Database functions of Excel i.e. functions like DSUM, DGET, DPRODUCT etc.
    You will get extensive help in the HELP Menu.

    Hope this information helps.

    Regards,

    "Mike" wrote:

    > I have a workbook that uses over 3000 sumproduct formulas to extract
    > data based on 7 different criteria. Works great except for calculation
    > time.
    >
    > Is there an alternative to Sumproduct that would be faster and still
    > allow for sumation based on multiple criteria?
    >
    > Is it possible to have Excel calculate certain cell ranges but not the
    > entire workbook?
    >
    > Would additional RAM help? (I currently have 1 MB)
    >
    > Is there a way to identify things that make calc time longer?
    >
    > Thanks for any suggestions...
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1