• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

array formula tutorial

koi

koi

Member
hi guys,


i'm curious about array formula, do you have any source/tutorial that i can read about it?


i dont really like it much since if i accidentally click on that cell.. the array is gone, and it ruin the formula, but i think it will be useful if i know the basic of array then maybe i can protect that sheet so nobody will ruin the formula


thanks a lot
 
Hi,


Yes, they need to be used with caution because a lot of users don't understand them and they can slow your spreadsheets down if overused or used incorrectly.


Here's a tutorial I wrote 5 years ago on array formulas:

http://www.xtremevbtalk.com/showthread.php?t=296012


At the end of the tutorial there is a links section which has links to other array formula tutorials which were available at the time.
 
it would be good to learn more about how arrays work and when they are usefule/needed. (Chandoo has written about arrays before...use the search box and im sure you'll find something!!)


if you just need to know how to edit a formula and NOT lose the array part: all arrays are entered using the Ctrl+Shift+Enter combination. Technically your write an array formula the exact same way as a regular formula. the only difference is how you 'enter' the formula into the cell. if its to be a 'normal' formula you can simply hit 'ENTER'. however if it is to be an array, you have to use the Crtl+Shift+ENTER combination. The trick is this applies if you ever edit a formula!! For instance, if you go to edit an array formula and when youre done you just hit ENTER, the formula will no longer be an array. you have to / must always use the CSE (ctrl+Shift+Enter) Combination.


hope that helps
 
Koi


Array Formula are one of the more difficult concepts in Excel to teach

You may want to have a look at:


http://chandoo.org/wp/category/formula-forensics/

In the Formula Forensics series array formula are used a lot and real life examples are pulled apart to see how they work
 
thanks all guys,


dont know if i can understand it but it is better to have something than nothing :)
 
You must log in or register to reply here.
Back
Top