Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    which is faster?

    Hi All,

    Per another thread, I have a spreadsheet where recalc time is of concern - already seeing the "Calc'g x%" msg on the taskbar when updates are made.

    Right now, I have a working spreadsheet with an array formula that looks like the following:
    {=IF(LEN($E3)<2, H2, IF(G3<>"Y", H2, IF( SUM(IF(($E$1:$E2=$E3)*(G$1:G2="Y"), 1, 0)) > 0, H2, H2+1)))}

    What this formula is doing in the SUM(IF... part is summing, for a given row, whether the name in E3 occurs anywhere above this row AND whether any of those above occurrences have a "Y" in the corresponding row's col G. If so, just copy H2; if not, increment what's in H2 for the current row.

    This formula is filled down for about 850 rows. Note that the "anchor" stays in row 2 but the 2nd part of the range continually changes to look at all rows starting at row 1 until the row just above the current row.

    The contents of what's being examined are not changing based on other things, so the calculations done by the formula are done once.

    I'm thinking of changing that array formula into the following:
    =IF(LEN($E3)<2, H2, IF(G3<>"Y", H2, IF( SUMPRODUCT(($E$1:$E2=$E3)*(G$1:G2="Y")) > 0, H2, H2+1)))
    where the SUMPRODUCT has replaced the SUM(IF... but the idea is basically the same.

    I've always heard that array formulas can be slow. That's one motivation for the change. But, again, the cells that are the precedents for either formula are not changing.

    While I think the 2 are equivalent and the first pass with the SUMPRODUCT seems to give the same result, the first formula has been working while the new one has just been tested once.

    Any thoughts on equivalency (I think they're the same) and speed?

    TIA

    Fred

  2. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Fred,

    "The contents of what's being examined are not changing based on other things, so the calculations done by the formula are done once." . . .
    . . . but of course it is all recalculated every time something else changes in the Workbook, regardless of whether the precedents have changed or not.

    In a situation like this I would do these specific calculations in a vba module and run it on opening the Workbook (and assign it to a button so I could run it again if, for instance, I changed the precedent data).

    This approach should revolutionise the speed of "using" the Workbook.

    Martin
    Last edited by MartinM; 2015-01-25 at 14:29.

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Fred

    ..I've often used 'master formula cell(s)', where, I used vba to 'copy the formula(s) down', and then 'converted the formulas to values'. If the 'prior data' doesn't change, then saving 'calculated formula results' as 'values' can significantly reduce subsequent calc overheads. If necessary, by maintaining only a single copy of the formula(s), you can always 'manually' copy the formulas down yourself etc etc.

    zeddy

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Hi Martin,

    Sorry, I did not mention explicitly - can't use VBA. That other thread to which I referred (no way for you to know) that this was for a friend running Excel 2008 on a MAC. You should see some of the comments there.

    http://windowssecrets.com/forums/sho...nd-col-and-row

    I'd bet VBA would make it go faster but I can't use VBA. And worst of all, she's happy even with the 1/2 second delay bcs it's a heck of a lot better than the old way of doing things.

    I wasn't sure if ALL calcs are redone every time something changes in the workbook. I thought Excel was smarter than that - it only recalcs things that are affected by the changed cells.

    So the original question remains - SUM(IF array formula vs SUMPRODUCT regular formula.

    Fred

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Hi Zeddy,

    I've done something like what you suggested - save a formula in a first row of a column w/o the equal sign and add a comment to explain what I did in case I need to restore the formula and fill. I then blank out the comment by making the font color the same as the background.

    Or I could use your comment on use of the BIF N for commenting formulas.

    As to whether the cells that I thought were static would ever change, I'd like to believe that to be the case. Two things I already know suggest that may be true only 99-44/100 % of the time:
    - the people who provide the data are not too good with the data entry (no DB involved) so names may be spelled in >1 way for at least a few cases - say 10-15 names out of 250. Then I need to do some triage to make things work.
    - at the very least, things will change next year, so I'd go thru the exercise again. Since I'm trying to force them to use a DB, maybe having things work better than the paper-pencil approach is good but not so good that they won't want something better.

    But if things do get bad, I like the idea of doing a paste special | values for things that don't change.

    Fred

    Or I could use your comment on use of the BIF N for commenting formulas.

    Fred

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    IIRC, Excel 2008 can use COUNTIFS so that should be better:

    =IF(LEN($E3)<2, H2, IF(G3<>"Y", H2, H"+(COUNTIFS($E$1:$E2,$E3,G$1:G2,"Y") > 0)))
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Fred

    ..why not persuade the MAC user to upgrade to Excel2011?
    ..then you can use vba, record macros, assign macros to shapes and buttons etc etc.

    zeddy
    Last edited by zeddy; 2015-01-26 at 04:38.

  8. #8
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Fred,

    Sorry I forgot about the vba, but there's a simple workaround !

    Make a copy of the Workbook and, in the copy, convert the relevant formulas to values. Then you can use this copy as the "working Workbook" - it will run much faster.

    When you need to change the data, update the original and make a fresh copy.

    It's almost as if you have a "run-time" version of the Workbook.

    Martin

  9. #9
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Hi Rory,

    Since I'm still doing my main work on Excel 2003, it doesn't have COUNTIFS. I do actually have Excel 2010 loaded on my computer (and just got 2013 but not loaded yet), so it's a possibility.

    2 quick questions:
    - is that an extra quote " in ...H"+(COUNTIFS...? not sure what this is doing. Should it be H2+(COUNTIF... to add 0 or 1 depending on whether the COUNTIF is false or true?
    - why couldn't I use something like H"+(SUMPRODUCT... >0)))? Would that not give me much of an improvement compared to COUNTIFS?

    Thanks.

    Fred

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Sorry typo - yes it should be H2, not H"

    COUNTIFS should be faster than SUMPRODUCT generally. You could also use another helper column that concatenates columns E and G and then just use COUNTIF.
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Hi Zeddy,

    Not a bad idea. Is Windows VBA the same as MAC VBA?

    Then I'll be back on here asking how to do x in VBA, since I'm not great w/ it. But it would be a good experience and certainly make things better. Of course, I don't want them to be too much better since I'm trying to get this into a DB.

    Know any good MAC DB programs? I've done work in Access but it's not available in MAC Office. I've convinced her that is the way to go in the future. We even spoke about having a separate Windows partition on her computer.

    Of course, laying out money for MAC 2011 Office is not something she wants to do - certainly not before getting a new (MAC - ugh) computer.

    Fred

  12. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Fred

    I don't have MAC Excel2011, but I know you can record macros in it. The MAC VBA has the same environment as the Windows VBE as far as I can tell.
    MAC Excel has the Developer ribbon, which shows Form Controls (buttons, checkboxes, radio buttons, list boxes etc etc)
    I have developed Excel2010 vba spreadsheets which worked on Macs that had a Windows op system set up.

    zeddy

  13. #13
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Hi Martin,

    Not a bad idea - it's like program code (the version with the formulas) and, as you said, an executable (the version with the values). Will have to think on this.

    While I don't anticipate it much, this puts the burden on me to "recompile" when a change is needed. And while there haven't been many, there have been a few cases of sloppy input that have made me need to do some work-arounds.

    Besides, we're almost at the end of this for this cycle of use. See post #4 here from my other response to you for an explanation of the application in that other thread. So we'll see what we can do next year. Apparently this has been a big hit with her and her teachers compared to the paper-pencil-erasing approach of the past.

    Fred

  14. #14
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Hi Zeddy,

    You lost me.

    If MAC Excel 2011 has VBA and it has "the same environment as the Windows VBE...", why would she need a Windows OS set up on the MAC? Couldn't I just take the file from my Excel 2003 or 2010 (an xls or xlsm), stick it on a thumb drive, and it would work on her MAC? That's what we've been doing so far for Windows Excel 2003 with MAC Excel 2008. Other than a few things (like colored tabs), it hasn't been too bad so far.

    Of course, one of the problems could be that her computer is 6 or 7 years old, so it's slow. But even on my 3-4 year old computer, I see the Calculating msg in the Status Bar more than ever before.

    Now if I could only convince her to get a brand new, Windows computer, with Excel 2013. Alas, I will only get one or two of those at best.

    Fred

  15. #15
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    May give that a try. Right now if the column does not have a "Y" in it, it has nothing. So I'd be looking to see, for example, if the helper column has "YRory" above the current row.

    But why not use just COUNT>0 so it becomes H2+(COUNT(...)>0) ? If "YRory" has appeared already, then I want H2's value; if not, I want H2+1.

    << forget the above paragraph - can't use COUNT >>
    Fred
    Last edited by fburg; 2015-01-26 at 15:32. Reason: momentary lapse in thought

Page 1 of 2 12 LastLast

Posting Permissions

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