Excel Array Formula Series #1: Basics of Array Formulas

This is the example I found:
Code:

Assume: Column A consist a list of data with blank row (in this example: A5, A7, A10, A11)
A2: Planning 
A3: Your
A4: Career
A5: 
A6: Change
A7: 
A8: Identify
A9: The goals
A10: 
A11: 
A12: Pay attention
A13: to what you see
Go to Insert menu | Name | Define
In the Define Name dialog box, enter the following in the 'Names in workbook' box BlanksRange
Then enter the following formula in the "Refers to" box =Sheet1!$A$2:$A$10
Click Add
In the Define Name dialog box, enter the following in the 'Names in workbook' box NoBlanks
Then enter the following formula in the "Refers to" box =Sheet1!$B$2:$B$10
Click Add, then OK
Type the following formula in cell B2:

=IF(ROW()-ROW( NoBlanksName)+1>ROWS( Name)-COUNTBLANK( Name),"",INDIRECT( ADDRESS(SMALL((IF( Name"",ROW( Name),ROW()+ROWS( Name))),ROW()-ROW( NoBlanksName)+1),COLUMN( Name),4)))

This is an array formula, so we must press Ctrl+Shift+Enter rather than just Enter when first enter the formula, and when ever we edit it later. Excel will automatically add the braces. 
If we enter or array formula without pressing Ctrl+Shift+Enter, it will return an incorrect result or a #NUM! error. -- which is easily overcome by pressing F2 as if to edit the cell's contents, then Control-Shift-Enter to re-enter the formula correctly. It will display:

{=IF(ROW()-ROW( NoBlanksName)+1>ROWS( Name)-COUNTBLANK( Name),"",INDIRECT( ADDRESS(SMALL((IF( Name"",ROW( Name),ROW()+ROWS( Name))),ROW()-ROW( NoBlanksName)+1),COLUMN( Name),4)))}

Then copy this formula to cell B3: B13, this will result:
B2: Planning 
B3: Your
B4: Career
B5: Change
B6: Identify
B7: The goals
B8: Pay attention
B9: to what you see
No blanks are copied to column B. Hope this formula what are you looking for.

But when I put it on page 4 and test it, it doesn't work. For all I know it may never have worked because it was on yahoo answers. Can you help me make one that works for the same thing?

I have a table with an if formula that brings a list out to the right of the original list, I need this formula to take that list to another page and take all the spaces out of it. Is it possible to do this without vba?

Lets say I have this....


PHP Code:


Dim B as Range

set B = activesheet.range("A1:C3")


Dim a(3) As Double

a(1)= 1.2
a(2)= 3.6
a(3)= .27 




Note: B is a 3x3 Matrix of values in your sheet

How would I perform matrix multiplication of B * array "a"?

My main problem is with how to reference then entire array "a" and how the data is being stored (is the array a 3x1 or a 1x3). And if there is a VBA MMULT command or if building my own or using the worksheet one are my only options.

Thanks

Hello;

1) I need to calculate M43:: sum[m=1 to m=9] G(m)*sin(m*x) for each value of tabulated x in column L.
The 9 values of "m" are tabulated in $D$21:$D$29
The 9 values of G(m) are tabulated in $C$21:$C$29
The first x value is in cell L43

Can someone please help in deriving the array formula to be entered in M43 ??
For other values of x in L44, L45, ... one would simply copy M43 and paste in M44, M45, ...

2) If it's not too much trouble, how about:
N43:: -sum[m=1 to m=9] G(m)*cos(m*x)/m for each value of tabulated x

Your help would be greatly appreciated.

Regards.

Hi all,

I have a table with 250 rows and columns A-AD. Each row has different types of information about a "bank paper". The table must be unsorted due to several reasons. I have to summarize/count the papers type by type. For example coloumn AD has the information if the paper's type is "NEW" or "DELETED". Also AC has the information if it is paper "Type 1" or paper "Type 2" etc.

I have to do many counts like this: count how many NEW, Type 1 paper exist in the table. I do this with array formulas since there are many aspects I should "filter", but it is too slow.

My question is: is it possible to collect the correct rows, for example all "NEW" rows into an array somehow? I want this to fasten up the search, since now the array formulas for finding NEW & Type 1 and NEW & Type 2 searches two times for NEW, what would not be necessary if I could collect all NEW rows somehow into someting like an "array-cell".

(I tried to do it with OFFSET, MATCH, INDIRECT and Ctrl-Shift-Enter with no luck, also thought about INDEX but I think these formulas are not for this.)

I have Excel 2003 (and two days ).

How to create a number series within an array formula, I mean, the series starts with 1 and will finish with 8 for example;
{1;2;3;4;5;6;7;8} - It will be the result within my array formula.
In Aij matrix means i=1 to 8 {i+1;i+1;i+1;i+1;i+1;i+1;i+1;i+1}.
I need to retrieve it within an array formula.

How can I do that just using an array formula.



Luthius

I have a list like this:

Sheet1

  A B C 1 Alfa Status D 75 2 Bravo Status A   3 Charlie Status B 483 4 Delta Status B 78 5 Echo Status C   6 Foxtrot Status A   7 Golf Status B   8 Hotel Status B 12 9 India Status A   10 Juliett Status A 405 11 Kilo Status C   12 Lima Status B   13 Mike Status A   14 November Status B 27 15 Oscar Status B 13 16 Papa Status B   17 Quebec Status C 140 18 Romeo Status B 426 19 Sierra Status C 484 20 Tango Status D 197

Excel tables to the web >> Excel Jeanie HTML 4

I want to single out all Status B values, and put them in a new table in descending order.

In this case, it would look like this:

Sheet1

  A B C 10 Charlie Status B 483 11 Romeo Status B 426 12 Delta Status B 78 13 November Status B 27 14 Oscar Status B 13 15 Hotel Status B 12

Excel tables to the web >> Excel Jeanie HTML 4

So I need to ignore blank values, determine which row has the highest quantity, and then return the value in column A and column C.

I know I can use array formulas to a certain extent, but I'm stumped as to how to store the row of the highest values in the array while ignoring blanks. Any ideas?

Hello everyone,
This is my first post, I am trying to build a quote templete for work that will take the lowest price quoted and plug that information into one column. I know it can be done because where I worked before we had a excel work sheet that did this. Can anyone point me in the right direction?
Sincerely,
Declare62

Hi there

I have many array formulas from Cell B2 to Cell E25000 in an Excel worksheet called results. These array formulas take on values based on another worksheet called STYLE-COLOUR. When I update the worksheet called STYLE-COLOUR the arrray formulas in the RESULTS sheet is taking around 20 minutes to calculate. Is there a way to shorten the time??

Mario

Hello,
I have a summary due tomorrow on a spreadsheet tracking different types of documents that I am reviewing. I need to begin calculating how many types of documents I am reviewing and how many are Active and New, plus how many reviewed per type per month overall . I am trying to put a summary page together with the totals per month and year. The spreadsheet that contains all the data has over 700 items reviewed, and there are 51 document categories. I have two array formulas to sort the NEW + ACTIVE items and another array to sort the Month and Type. I need to count how many are NEW + Active of each type per month, then a Year to date total for each category.

Here are the formulas I have so far for the arrays which work (I only have one category done so far out of 51).

for NN=New and active
Array 1
=SUM(IF(H3:H1210="NN",IF(J4:J1210="ACTIVE",1,0),0))

for month and type

Array 2

=SUM(('VM Files info'!C3:C777=" APRIL ")*('VM Files info'!M3:M777=" ACK "))

April= month
Ack= abbrivation of document type

The columns containing the data are in the same workbook but on a different sheet.
The cloumns I need to collect data on for the summary a
C -Month
M- Document Type
H- New or copy
J- Active or Expired.

Is sum product the right formula to use? I am complety stuck and can't figure this one out.
I tried:
=SUMPRODUCT((=SUM(IF(H3:H1210="NN",IF(J4:J1210="ACTIVE",1,0),0))* =SUM(('VM Files info'!C3:C777=" APRIL ")*('VM Files info'!M3:M777=" ACK "))

to no avail.

thank you for your help and taking the time to read my post.

For those who do not have a background in programming or mathematics, the expression Array may not be familiar.
So what exactly is an Array?
For our purposes, an Array is simply a set of values which can be stored in a formula, a range of cells, or the computers memory.
The size of an Array can range from two values to thousands.



There are several different types of Arrays used by Excel when working with formulas:
An Array stored in a Worksheet in a range of cells: For example, when the SUM function sums the values stored in a range of cells, it is treating those values as an Array.



Instead of entering cell addresses to enable a formula to operate on the values stored in those locations, you may enter an Array of values into the SUM function arguments: =SUM(1,2,3,4,5).
Or
Enter an Array enclosed in brackets into the formulas argument. For example, use the MATCH function to return the position of the number 10 in an Array of values: =MATCH(10,{3,7,10,15,20}). The result = 3



Formulas such as SUMPRODUCT utilize computer memory to store values temporarily while calculating complicated math problems. These values are stored in an Array.


To add the total sales amount of 3 items when the quantities sold are 10, 20, and 30 and the sale prices are $3, $4, and $5 respectively, the SUMPRODUCT formula stores each multiplication product in an Array (Array size is 3) and then adds the three values from the Array.
The SUMPRODUCT formula: =SUMPRODUCT(A1:A3,B1:B3),
Result - total sales=$260.



As explained in the previous section, many formulas create Arrays when they need to store values during calculations. However, an Excel user may create a formula that deliberately enforces the program to open an Array/Arrays to store values.


Use the SUM function to return total sales (see previous example). The formula will now look like this: {=SUM(A1:A3*B1:B3)}, Result- total sales=$260.


Enter the formula, select the cell, press F2, and then simultaneously press Ctrl+Shift+Enter.