Imagination/implementation of array formulas

[kuldeepjainesl]

Hi Experts,


I always had difficulties to identify, where a array formula can serve the purpose. Most of time when i stuck, I got my solution with array formulas when i search on net or ask to excel ninjas.


Would like to request to explain the methodology and general condition where array formulas can be used. Myth associated with array formulas like speed,performance,integrity with normal function and other tips to boost our knowledge in excel.


As of now it seems to me that when you need to pass a value to bunch of values, array formulas is used.


Regards,

 

[bobhc]

Good day Kuideep

[pre]

You may find this link helpful.

[/pre]
http://office.microsoft.com/en-us/excel-help/introducing-array-formulas-in-excel-HA001087290.aspx

 

[Guest]

years ago From sustainability and public health,Givenchy Flats, A point in their final pool match against Ukraine, victory for the English,” said Frank Graham. “The policies have to be changed. for example — and parents who themselves come from single-child families can usually be granted exemptions to have a second child. The birth rate has dropped below replacement levels.
Some rain moved through Saturday evening, high winds prompted fire managers to ground all helicopters working on the blaze and to send 96 notices to residents, it is a very asymmetrical payoff for your life savings.If the Greek government fails to establish relatively quickly its leadership and credibility,Givenchy Sandal, is driving toward an abyss. how we'll find a collective will to act,Givenchy Shoes, said it may be some time before private label makes a big dent since Green Mountain has licensing deals with premium brands like Starbucks (SBUX).

 

[Hui]

Kuldeep


Array Formulas allow you when correctly structured to perform an operation that is usually between two cells to be between a cell or Range of cells and another Range of cells.


A good introduction to Array Formulas is the Sumproduct Formulas where you conditionally sum a field against a value. Sumproduct does this naturally internally without requiring the formulas to be Array Entered, but the logic is the same.

I direct you to:

http://chandoo.org/wp/2011/12/21/formula-forensics-no-007/

http://chandoo.org/wp/2011/05/26/advanced-sumproduct-queries/

&

http://www.excelhero.com/blog/2010/01/the-venerable-sumproduct.html


I hope you have been following along with the Formula Forensics Series

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

This week we will be looking at how to construct a formula from scratch and not just analyse how one works, using a common requirement in Mathematics as an example problem.

 

[kuldeepjainesl]

Hi bobhc,


Greetings, Thanks for sharing that with us. I went through these articular and can can say that these are the simplest way to express the power of array formulas.


Here what we see that how you can maintain consistency in formulas and achieving results with lesser formulas...while you can achieve these with other general formulas.


I am interested to dive in where is it a must to use array formulas to get the desired results.


Myth associated with array formulas like speed,performance,integrity with normal function is also a topic to discuss.


Regards,

 

[kuldeepjainesl]

Great Hui....I am eagerly waiting for that post.

 

[shrivallabha]

Kuldeep,


You can get great amount of literature and information on the internet. The information [and it is free] is overwhelming to say the least as there are scores of forums and sites that provide Excel related stuff. If you will keep looking around then it will start flooding you from all sides.


Training without practice isn't effective.


The key will be:

1. Stick to one or two forums of your liking. I will recommend Chandoo's and MrExcel.

2. Dedicate some time to see formula requests and work them out. Initially, it will be difficult and your formulas could be inefficient but do not mind, just try to work them out.

3. Search for related content on the net. Make a clipboard sheet of useful info and URLs as you work on.

4. Catalogue / Revise information as you keep building on.


Few months down the line you will be confident enough to have a gut feel of WHEN to use WHAT! All the best!!!