Results 1 to 7 of 7
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Assistance with conditional formulas (Excel 2003)

    Hi,

    I need some assistance with some conditional formulas. There is 8 criteria to calculate. I have done them all but I am aware that array formulas are very processor intensive. The attachment is a sample sheet, but the real file contains over 10500 records. The arrays are working but very slow.

    Is there variations of these formulas but NOT using arrays.
    Many TX
    Attached Files Attached Files
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Assistance with conditional formulas (Excel 2003)

    The following are all 'normal' (non-array) formulas (the array aspect is implicit in SUMPRODUCT):

    In I6:

    =SUMPRODUCT((G13:G151<>"White")/COUNTIF(B13:B151,B13:B151))

    In I7:

    =SUMPRODUCT((G13:G151<>"White")*(H13:H151="Female" )/COUNTIF(B13:B151,B13:B151))

    In I8:

    =SUMPRODUCT(L13:L151*(G13:G151<>"White")*(H13:H151 ="Female"))

    In I9:

    =SUMPRODUCT((G13:G151<>"White")*(I13:I151="YES")/COUNTIF(B13:B151,B13:B151))

    In I10:

    =SUMPRODUCT(L13:L151*(G13:G151<>"White")*(I13:I151 ="YES"))

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Assistance with conditional formulas (Excel 2003)

    With some further experimentation I have noticed that it is only the {=SUM(1/COUNTIF($D$13:$D$151,$D$13:$D$151))} formula that is REALLY slooooow. Is there maybe just a variation on this one?

    Cheers
    Regards,
    Rudi

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Assistance with conditional formulas (Excel 2003)

    TX Hans,

    I'll try these out on the real file.

    Many TX
    Regards,
    Rudi

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Assistance with conditional formulas (Excel 2003)

    The alternative would be to use a custom VBA function, but I don't think this will be faster. Counting uniques in a large list is time-consuming.

    You could turn off automatic recalculation, and only recalculate the sheet when you need up-to-date results.

    Or you could migrate the data to a database (Access or SQL Server), and use totals queries.

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Assistance with conditional formulas (Excel 2003)

    Advice taken.
    TX for your help. Appreciated.
    Regards,
    Rudi

  7. #7
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Assistance with conditional formulas (Excel 20

    One of good methods to speed up multiple condition SUMs is:

    Split out the multiple conditions into a column of helper formula, that return TRUE(1) or FALSE(0) for each row,

    then, reference the helper column in a SUMIF or SUM formula

    After added the helper columns at column N to column S, as per the attached file

    Herein , the OP's formulae replaced by the advice formulae

    1] Cell I3 =L152

    Replaced by : =LOOKUP(9.99E+307,L:L)

    2] Cell I4 =SUM(1/COUNTIF($D$13:$D$151,$D$13:$D$151))

    Replaced by : =SUM(Q13:Q151)

    3] Cell I5 =SUMIF(G13:G151,"<>White",L13:L151)

    Replaced by : =SUMIF(N13:N151,1,L13:L151)

    4] Cell I6 =COUNTIF(G13:G151,"<>White")

    Replaced by : =SUMIF(N13:N151,1)

    5] Cell I7 =SUM(IF(G13:G151<>"White",IF(H13:H151="Female",1,0 ),0))

    Replaced by : =SUM(R13:R151)

    6] Cell I8 =SUM(IF(G13:G151<>"White",IF(H13:H151="Female",L13 :L151,0),0))

    Replaced by : =SUMIF(R13:R151,1,L13:L151)

    7] Cell I9 =SUM(IF(G13:G151<>"White",IF(I13:I151="YES",1,0),0 ))

    Replaced by : =SUM(S13:S151)

    8] Cell I10 =SUM(IF(G13:G151<>"White",IF(I13:I151="YES",L13:L1 51,0),0))

    Replaced by : =SUMIF(S13:S151,1,L13:L151)

    Regards
    Bosco
    Attached Files Attached Files

Posting Permissions

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