Closed Thread
Results 1 to 33 of 33

Activating Multiple Array Formulas at Once...

  1. #1
    Registered User
    Join Date
    04-10-2009
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    39

    Activating Multiple Array Formulas at Once...

    Hi,

    Does anyone know how to activate a block of different array formulas at once??

    Example:

    N7:Q80 has a total of 296 Array Cells. Each has a unique formula & I cannot just drag to fill these nor can I activate all at once.

    In the future, I don't want to have to manually activate them w/F2, CTRL+SHIFT+ENTER.

    btw, Why do I have to press F2? Is that only in Excel 2007? I googled pretty extensively & don't see an option how to only press CTRL+SHIFT+ENTER. It would be nice not to have to press F2 everytime.

    Does anyone know a short cut to this?

    Thanks
    Last edited by pipsturbo; 05-14-2009 at 11:43 AM.

  2. #2
    Forum Contributor mubashir aziz's Avatar
    Join Date
    03-18-2009
    Location
    Lahore, Pakistan
    MS-Off Ver
    MS Office 2013
    Posts
    533

    Re: Activating Multiple Array Formulas at Once...

    There is no need to press Ctrl + Shift + Enter every time unless you make some change in formula .........
    If this post helps, Please don't 4get to click the star icon located at the bottom left of my Post.

  3. #3
    Registered User
    Join Date
    04-10-2009
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    39

    Re: Activating Multiple Array Formulas at Once...

    Right, I understand what you mean but the 1st time, when I go to activate, I have to press F2 then CTRL+SHIFT+ENTER.

    Is there a way to just do CTRL+SHIFT+ENTER & also do you know how to activate many at once?

    Thanks & Shukra

  4. #4
    Forum Contributor mubashir aziz's Avatar
    Join Date
    03-18-2009
    Location
    Lahore, Pakistan
    MS-Off Ver
    MS Office 2013
    Posts
    533

    Re: Activating Multiple Array Formulas at Once...

    Quote Originally Posted by pipsturbo View Post
    Right, I understand what you mean but the 1st time, when I go to activate, I have to press F2 then CTRL+SHIFT+ENTER.
    Is there a way to just do CTRL+SHIFT+ENTER & also do you know how to activate many at once?
    Thanks & Shukra

    I've completely lost now ..... First time when you make array formula then you have to use CSE .... then in future if you are making some changes in relative cells then there is no need to go in formula and press Ctrl + shift + Enter ...... First time in all cells you have to use CSE in which you are putting Array formula ............

    Or there is one possibility that may be you have made calculation manual instead of Automatic so you have to press F9 or refresh the formulas .... You can make it automatic by Tools > Option > Calculation - Automatic .....

  5. #5
    Registered User
    Join Date
    04-10-2009
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    39

    Re: Activating Multiple Array Formulas at Once...

    Hi Mubashir,

    Here's what I'm asking:

    1) Can I activate array calculation across many cells w/o having to go through manually & do CSE for all?

    if I can't do #1, can I do #2?:

    2) Currently, before I do CSE, I have to press F2. Can I disable the F2 part?

    Thanks,

    Mike

  6. #6
    Valued Forum Contributor squiggler47's Avatar
    Join Date
    02-17-2009
    Location
    Littleborough, UK
    MS-Off Ver
    Excel 3.0 to 2007+2010! (couldnt get 2.1 working)
    Posts
    1,013

    Re: Activating Multiple Array Formulas at Once...

    have you tried just pressing F9 to recalculate sheet?

    CSE jsut enters the formula

    F2 edits the formula

    once the formula has been CSE'd the first time, you never have to do it again until you change the formula

    I am guessing calculation is set to manula since there are so many array formulas

    So F9 would seem logical!
    Regards
    Darren

    Update 12-Nov-2010 Still job hunting!

    If you are happy with the results, please add to our reputation by clicking the blue scales icon in the blue bar of the post.

    Learn something new each day, Embrace change do not fear it, evolve and do not become extinct!


  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Activating Multiple Array Formulas at Once...

    I think the premise is OP has x Arrays with Arrays not set - each needs to be set - given the Arrays are unique each must be activated independently... this is a time consuming task obviously. Thereafter it is not an issue... it sounds like a one off task.

    Pending the string length of the formulae you could use a VBA Routine to iterate the cells and activate each Array, eg:

    Please Login or Register  to view this content.
    Though I think the more pertinent question would be: why so many arrays ? Arrays en masse = poor overall performance.

  8. #8
    Forum Expert dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Smile Re: Activating Multiple Array Formulas at Once...

    Good morning pipsturbo
    Quote Originally Posted by pipsturbo View Post
    In the future, I don't want to have to manually activate them w/F2, CTRL+SHIFT+ENTER.
    Does anyone know a short cut to this?
    I don't beleive there's a shortcut, but this macro might do the trick :
    Please Login or Register  to view this content.
    Just highlight the cells you want to convert to arrays and run it.

    HTH

    DominicB
    Please familiarise yourself with the rules before posting. You can find them here.

  9. #9
    Forum Contributor mubashir aziz's Avatar
    Join Date
    03-18-2009
    Location
    Lahore, Pakistan
    MS-Off Ver
    MS Office 2013
    Posts
    533

    Re: Activating Multiple Array Formulas at Once...

    Now there are many experts to solve your problem but they main things is still not clear that which type of prolbem you have .....


    Anyway, Sample work book can give us some idea.

    About you query 1
    Yes, you can apply Array formula on ranges like A1:A10 and you don't need to go on these cells now you can drag down the formula is other cells and there is no need to do CSE in each cells but first time you have to.

    For your Query 2 ... do the below steps
    Tools > Options > Edit > Uncheck Edit Directly in Cells
    Now F2/Double Click will not take to you in Cell but in Formula Bar
    Now do the below step
    Tools > Options > View > Uncheck Formula Bar
    Now double clock or F2 will not allow you to edit
    But a person who know about this can easily figure out so best thing is to protect sheet and protect formula's cell and unprotect other cells

  10. #10
    Valued Forum Contributor squiggler47's Avatar
    Join Date
    02-17-2009
    Location
    Littleborough, UK
    MS-Off Ver
    Excel 3.0 to 2007+2010! (couldnt get 2.1 working)
    Posts
    1,013

    Re: Activating Multiple Array Formulas at Once...

    Yes it may be a good idea to post a sample of your book (with sensitive data removes)

    We may be able to clean it up for you and solve all your problems in one go

    296 different array formulas seems excessive to me!

  11. #11
    Registered User
    Join Date
    04-10-2009
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    39

    Re: Activating Multiple Array Formulas at Once...

    First of all, thanks for the responses, I appreciate it.

    1) Calculation is Auto, so I don't need to press F9 now. For some reason, I have to press F2 before I am able to do the whole CSE routine. I was following Mubashir's idea of changing the setting in the Tools location but that's in 2003 & I'm using 2007. I tried going through the Excel Options & disable Edit Directly in Cells but I still have to use F2.

    2) Yes, I agree Donkeyote, too many will make it sluggish but I'm slowly evolving & I dk VBA much, only recording macros. I want to learn a programming language to automate a lot of processes that I have to do; like C# or maybe VBA, not sure yet.

    I'm currently running the VBA code, Dominicb. I much appreciate it.

    I will upload the book if the arrays crash Excel. (It might, it's been chugging away for about the last 10 min...)

    I'm guessing that when I go to alter the underlying data that is being analyzed that the array recalculation might be pretty taxing on it too.
    Basically I'm doing a statistical study of a very large amount of data & I need specific statistical studies for different types of data points.

    I will post back in a little bit when the code is done running & I can change the values...

  12. #12
    Registered User
    Join Date
    04-10-2009
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    39

    Re: Activating Multiple Array Formulas at Once...

    Hi,

    So I ran the VBA code & it did update all the array cells to function but made Excel cough & wheeze for a while.

    I'm attaching the workbook that I'm looking at.

    The idea is that I am analyzing data for relevance to columns G & H (I removed these values & put 1s, as it's sensitive data. I shortened the data from ~20k to 1k & cut out the array formulas except for 1 row because it was too slow.

    Is there a way to run a VBA script or another more automated method for a range of dates & times then correlate their prices to values in columns G & H, since arrays are too cumbersome to be used in such quantity?

    It seems pretty clear now to me that having more than 8-10 array formulas, even though this isn't very complex, is a rather heavy load for Excel to bear.

    Thanks for your help,

    Mike

  13. #13
    Registered User
    Join Date
    04-10-2009
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    39

    Re: Activating Multiple Array Formulas at Once...

    Sorry, didn't attach the file... here it is =)
    Attached Files Attached Files

  14. #14
    Valued Forum Contributor squiggler47's Avatar
    Join Date
    02-17-2009
    Location
    Littleborough, UK
    MS-Off Ver
    Excel 3.0 to 2007+2010! (couldnt get 2.1 working)
    Posts
    1,013

    Re: Activating Multiple Array Formulas at Once...

    first thing I see is you are working with the whole column!

    which in excel 2007 means 1000000 rows!

    which you are calculating many times!

    I think a dynamic range would speed things up a little

  15. #15
    Valued Forum Contributor squiggler47's Avatar
    Join Date
    02-17-2009
    Location
    Littleborough, UK
    MS-Off Ver
    Excel 3.0 to 2007+2010! (couldnt get 2.1 working)
    Posts
    1,013

    Re: Activating Multiple Array Formulas at Once...

    i totally screwed that up even though my formulas worked

    ok names I did

    and new formulas

    here is book
    Last edited by squiggler47; 05-14-2009 at 06:29 AM. Reason: Cause i'm a dumb ***

  16. #16
    Registered User
    Join Date
    04-10-2009
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    39

    Re: Activating Multiple Array Formulas at Once...

    Hi Squiggler,

    How are you?

    Sorry, but I don't see any changes in the book I posted & the one that is attached to your post.

    Did I miss smth?

    Thanks,
    Mike

  17. #17
    Valued Forum Contributor squiggler47's Avatar
    Join Date
    02-17-2009
    Location
    Littleborough, UK
    MS-Off Ver
    Excel 3.0 to 2007+2010! (couldnt get 2.1 working)
    Posts
    1,013

    Re: Activating Multiple Array Formulas at Once...

    Quote Originally Posted by pipsturbo View Post
    Hi Squiggler,

    How are you?

    Sorry, but I don't see any changes in the book I posted & the one that is attached to your post.

    Did I miss smth?

    Thanks,
    Mike
    I gave you some new formulas, not sure why the Correl() function returns Div / 0 I just recoded your functions to avoid the array formulas and to speed the sheet up!

  18. #18
    Registered User
    Join Date
    04-10-2009
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    39

    Re: Activating Multiple Array Formulas at Once...

    How does that work? Did you do that in VBA? It does seem a little quicker. I haven't tried to use on more than the 1st row yet. I will try now...

  19. #19
    Valued Forum Contributor squiggler47's Avatar
    Join Date
    02-17-2009
    Location
    Littleborough, UK
    MS-Off Ver
    Excel 3.0 to 2007+2010! (couldnt get 2.1 working)
    Posts
    1,013

    Re: Activating Multiple Array Formulas at Once...

    I think the problem is in how you are using the correl function

    Changing g or h to 0.9 or 1.1 results in the formulas working

    and I noticed about a 5 second delay with your formulas, mine seemed to work almost instantly

    not knowing what results you expect or correl() I cant help youu further

    its now in the realms of math!

  20. #20
    Valued Forum Contributor squiggler47's Avatar
    Join Date
    02-17-2009
    Location
    Littleborough, UK
    MS-Off Ver
    Excel 3.0 to 2007+2010! (couldnt get 2.1 working)
    Posts
    1,013

    Re: Activating Multiple Array Formulas at Once...

    I just used a range name, which limits rows a:h to only rows with data
    which eliminates having an array of 1000000 rows * the 3 tests you are doing in memory and processing them

    look at the named ranges to see what I did there

    Enter Date in the name box to highlight the date range

    I then use indirect to feed a smaller set of this to the formulas basically just the rows that are within the time date limits, bu calculating the first row within the limits and the last.

    This does of course assume that your data is in date time order!


    so correl only has to work with about 200 items saving memory and processing time

  21. #21
    Registered User
    Join Date
    04-10-2009
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    39

    Re: Activating Multiple Array Formulas at Once...

    I appreciate your help but I don't see the difference in the array formulas.

    Maybe I'm not looking in the right place.

    I don't want to be a pain though, you've been generous with your time.

    Thanks

  22. #22
    Valued Forum Contributor squiggler47's Avatar
    Join Date
    02-17-2009
    Location
    Littleborough, UK
    MS-Off Ver
    Excel 3.0 to 2007+2010! (couldnt get 2.1 working)
    Posts
    1,013

    Re: Activating Multiple Array Formulas at Once...

    I put the new formulas to the right of yours and left yours intact

  23. #23
    Registered User
    Join Date
    04-10-2009
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    39

    Re: Activating Multiple Array Formulas at Once...

    ahahhaa

    Sorry!!

    Wow! Works very well, thank you soooo much.

    You've been very patient...

    I appreciate it.

  24. #24
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Activating Multiple Array Formulas at Once...

    Thanks Dominic, I had almost 1000 different arrays each pointing to a separate worksheet. That just saved me a couple hours of CSE.

    Life is better with VB.

  25. #25
    Registered User
    Join Date
    12-24-2013
    Location
    Florida
    MS-Off Ver
    Excel 2016
    Posts
    18

    Re: Activating Multiple Array Formulas at Once...

    I am working with an excel sheet that has hundreds different array formula. I used Find and replace to modify the formula then I found I need to do the CSE for each cell.
    I tried your code but I got a error says "Run-time error '1004': Unable to set the FormulaArray properties of the Range class". The problem was from the line "UsrCell.FormulaArray = UsrCell.Formula"
    Any ideas?
    Thanks!

  26. #26
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Activating Multiple Array Formulas at Once...

    Are any of the formulas in merged cells? Merged cells can't be made into array formulas, though you can merge the cells of an existing array formula.
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  27. #27
    Registered User
    Join Date
    12-24-2013
    Location
    Florida
    MS-Off Ver
    Excel 2016
    Posts
    18

    Re: Activating Multiple Array Formulas at Once...

    No. I don't have any merged cells. Thanks!

  28. #28
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Activating Multiple Array Formulas at Once...

    Did you first select all of the cells you want to run this against? This macro is based on cells selected in advance.

    My post above was interestingly enough, my very first post in this forum last year. Such a long way I've come..

  29. #29
    Registered User
    Join Date
    12-24-2013
    Location
    Florida
    MS-Off Ver
    Excel 2016
    Posts
    18

    Re: Activating Multiple Array Formulas at Once...

    Quote Originally Posted by daffodil11 View Post
    Did you first select all of the cells you want to run this against? This macro is based on cells selected in advance.

    My post above was interestingly enough, my very first post in this forum last year. Such a long way I've come..
    I did select multiple cells first, then ran the Macro. I always get the error message no matter how many cells I chose.

    I was excited to found this post, hoping it would work on my sheet. Glad that you are still available!

  30. #30
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Activating Multiple Array Formulas at Once...

    Can you post a work sample, or at least fabricate something I can work with physically?

    Code really requires something concrete, otherwise I'm just guessing at what you're looking at.

  31. #31
    Registered User
    Join Date
    12-24-2013
    Location
    Florida
    MS-Off Ver
    Excel 2016
    Posts
    18

    Re: Activating Multiple Array Formulas at Once...

    Quote Originally Posted by daffodil11 View Post
    Can you post a work sample, or at least fabricate something I can work with physically?

    Code really requires something concrete, otherwise I'm just guessing at what you're looking at.
    Hi, I have attached a work sample (I have replaced my real data with random numbers).

    The data are in the sheet "Summary" and the statistics are in the sheet "Stats". In the worksheet "Stats", I modified the formulas in the "original table" by using Find and Replace. I only changed "=" to "#" then changed them back for this demonstration, so the results between the two tables should be identical.

    Now I need to do the CSE for every cell in the "Modified table". The Macro gives me the error.

    Thanks again for looking into this!
    Attached Files Attached Files

  32. #32
    Registered User
    Join Date
    11-14-2013
    Location
    Blue Mountains, Australia
    MS-Off Ver
    365
    Posts
    5

    Re: Activating Multiple Array Formulas at Once...


  33. #33
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Activating Multiple Array Formulas at Once...

    Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

    Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

    Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

    Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

    also


    Unfortunately your post(s) does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

Closed 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