An Array Formula is a formula or function on a spreadsheet application that utilizes an array in its syntax.

learn more… | top users | synonyms

0
votes
1answer
19 views

Excel 2007 - find max value based on status criteria in data list

I need to find the largest value (aging days) in a set of data based on the item status. Much like a SUMIF function, but a MAXIF (if one existed). My data is not sorted by status. I basically want ...
0
votes
1answer
30 views

Excel Formula Assist - Find missing numbers

I have a spreadsheet exported from my database that contains Roles and Account IDs. There are 4-5 role names per account ID. I need a formula to display any accounts that do not have a role of RBD. ...
0
votes
1answer
25 views

How to sum every second column depending on the first column value

Team, This is driving me mad. I know how to sum every second column in a row: ie {=SUM(A1:G1*(MOD(COLUMN(A1:G1),2)=0))} What I need to know is how to sum all of the first columns unless there is a ...
1
vote
1answer
35 views

Creating Dynamic, Unique Arrays Dependent on Multiple Criteria or Subset of Data

I have a Table structured in this manner: City Region Date Value ABC 123 Jan1 10% DEF 999 Feb1 7% The table is fed by user-entry, but there is an important rule: ...
0
votes
0answers
24 views

Return an array from a lookup

I have a table with column A containing vendors and column B containing product codes. One vendor has many product codes. In another place I need an array of product codes for a specific vendor. The ...
0
votes
1answer
53 views

SUMPRODUCT and IF conditional (LibreOffice) [closed]

I would like to multiply two cells in a same row, if a condition of a third adjacent cell is met; then sum the result to the next product in the next row (if condition is met), and so on. | A | B ...
0
votes
1answer
93 views

Excel Return Max Text Value Using VLOOKUP

I need to come up with a formula in excel that will identify the max and min software offered on a computer and input it into a new table. So for instance, in one large table I have column names ...
1
vote
1answer
37 views

Averageif with criteria being related to a different cell

I have an array of numbers in excel, and I want to get the average of all numbers where the column headers matches a pattern. Currently, I have data that looks like this: At the bottom I have an ...
-2
votes
2answers
48 views

identify the rows with the lowest values in an array based on identical values in a column of that array

I have an array of product data which includes code, description and cost. code is unique, cost is numeric and description is non-unique. Please see example. How can I select the cheapest of each ...
1
vote
2answers
49 views

Simple, Excel, =LOOKUP(xxxxx), not returning the correct value

OK this is infuriating as I know this should work... I have certain traits in column A. Each trait has a corresponding value in column B and a numeric indicator of that value in column C. Like ...
1
vote
1answer
54 views

Excel: What formula to use to return the minimum or maximum of a set of lookup values? [duplicate]

I'm looking for a way to calculate the values shown in cols D & E in the below screengrab. Column D contains the earliest date from column B where the name in column A matches. Similarly, column ...
1
vote
1answer
99 views

Can you define an array formula across multiple columns?

I have a table of Stores (well, for this example anyway): Store_ID Store_Name ... 1 High Street 2 Low Street ... I have another table of Employees: Employee_ID Store_ID Is_Active 1 ...
0
votes
0answers
29 views

How to link N*M cells in MS-excel? (F4 function for $ in a matrix)

I need to link several cells from a matrix. How can I do that? Normally I use to clikc F4 to insert $ symbols for lines/columns, but in this case I have to put it in each cell. Is there a shortest ...
3
votes
2answers
51 views

Define value of a column by another column in Excel

This seems like it should be so simple! I need to define column B by column A If column A is equal to any value in the array "taco,burger,spaghetti" column B should read "food", if column A is equal ...
0
votes
1answer
15 views

Calculating non duplicates

I have below data in three columns A B C 07002 72J217.4 72J217 07002 72J229.5 72J129 07002 72J229.6 72J129 07002 72J229.7 72J129 07006 61E001.5 61E001 ...
0
votes
1answer
44 views

Excel formula for increasing the row number in excel 2007

Please. The formula already posted for increasing the row number while dragging across columns.(=OFFSET($A$1,COLUMN()-1,) Want to drag down formula in rows, increasing the column letter, using same ...
0
votes
1answer
233 views

Excel formula to sum the total for a specific person in a specific month

I need help with an Excel formula that sums the total or work for a specific person in a specific month. Here is what my data looks like: I need to setup a formula to extract automatically the sum ...
0
votes
1answer
179 views

How to “Fill Down” more intelligently in Excel?

I have a formula in cell A1: =VLOOKUP(E2,Master!A2:C560,3,0) If I do "fill down" to paste this formula into all the cells below, the formula changes as follows: =VLOOKUP(E2,Master!A2:C560,3,0) ...
0
votes
2answers
45 views

Force a one-dimensional array into a constant in Excel

I have a condition in an Excel IF-statement as follows: INDIRECT(ADDRESS(ROW(ME3),MATCH("PremiumWaiver",$1:$1,0)))=1 where INDIRECT(ADDRESS(ROW(ME3),MATCH("PremiumWaiver",$1:$1,0))) eveluates to ...
0
votes
0answers
26 views

Excel 2007, how do I formulate a changing reference to find highest value within a pivot?

I need to find the most highest scorer with each change of a previous value, so the number of rows for each area differs: This is the sample: A B C D 1 OFFICE.. PERSON.. ...
0
votes
0answers
78 views

IF word in A1 matches word in Col C AND word in B1 matches word in Col E AND

Cell A1 contains a paragraph made of Hebrew words (They are Bible verses in Hebrew, so sometimes more than 254 characters long in each cell). Cell B1 contains a paragraph of English words (They are ...
-1
votes
1answer
29 views

using date ranges and numbers

I have a spreadsheet that uses both dates and numbers. For example I have a job that takes 4 hours to complete and needs to be done on 10/11/13. I have several jobs that end on this date but the time ...
2
votes
1answer
975 views

Excel array formulas to find smallest date with multiple criteria

The array formula I have will give me the smallest date from a Service table, based on the following criteria: 1) The value in the item column matches the item # in cell A1 2) The value in the ...
1
vote
1answer
56 views

Excel fomularbar shows correct Value, Sheet shows VALUE error

Here is the data I have been using, data format all General: Name Address Adam 250 Mohame 150 Adam 220 Adam 423 Yasir 240 Ibrahim 236 Adam 242 Index formula cell B11 contains the ...
0
votes
1answer
33 views

Transpose data by checking similar cell data

I have a Excel sheet like below.. Color Size Qty Red L 182 Red M 223 Red S 155 Red XS 30 Blue L 700 Blue M 845 Blue S 665 Blue ...
0
votes
1answer
955 views

In Excel I need to find data matches between two arrays - one horizontal and one vertical

In Excel 2010 (or 2007 - I have both, although my OS is only Win7 32 bit as a limitation for some legacy applications we run), I need to find how I can find and return the matching value from two data ...
1
vote
1answer
84 views

Using an array formula to add up several cells in another worksheet (using INDIRECT)

I have an Excel 2007 workbook. On one worksheet, I want to add up several regularly spaced values on another worksheet. I can easily do this with a very long sum formula without an array formula but ...
2
votes
0answers
180 views

VBA - Arrayformula to a range stored as array variable

I need to apply a formula array to dynamic ranges in my spreadsheets, which for a fixed size range works fine using for example: Selection.Offset(2, 0).FormulaArray = ...
1
vote
1answer
890 views

Dragging the index formula down when using arrays

On this older page, Dan O'Day provides and excellent excel formula to assist with looking up and returning multiple reference values for a given key in separate columns. The formula is: ...
0
votes
3answers
4k views

Excel: Using ADDRESS to create Array Formula

I'm trying to replace the hardcoded references E13:E15 with relative references: =+SUM(LN(INDIRECT("E13:E15"))) I've tried variations of below, in cell e16, but nothing seems to work: ...
4
votes
1answer
2k views

using an and() formula with an if statement in an array formula in excel

OK so I have a list of client returns in a spreadsheet. Each client also has a risk category assigned to the (risky or safe) and the partner assigned to them (P1 or P2). Here is the an example of ...
0
votes
1answer
58 views

How to edit a formula that I already have to take into account another cell's data

I have an Excel sheet that keeps track of bonus for our paralegals. Because we need the clients to be listed in one sheet all the paralegals are also in one sheet instead of each having their own. I ...
0
votes
1answer
150 views

LibreOffice Calc STDEV and arrays

was reviewing an "excel challenge" and found an question that I can obtain an answer with excel 2003+ but not in LibreOffice Calc 3.5.4.2. I'm interested in why the solution doesn't work in Calc but ...
2
votes
1answer
1k views

Using Excel Linest function with blank cells

I need LINEST function to be applied with blank cells to get the slope parameter as well as the associated standard error. I can get the slope parameter with a normal function that may resolve the ...
1
vote
1answer
163 views

Getting a MAX using AVERAGEIFS logic

I have a table of data that I want to find a MAX value for. The challenge is filtering the values I care about. Basically, I want the MAX from Column D when: Column A = 'VIC' Column B = 'CBD' ...
1
vote
1answer
1k views

Excel array formula for max with multiple/ variable criteria

G'day there, I have a sheet that contains 365 rows, representing dates from a referred Start Date (for the following year). There are about 30 columns each of which has an agency name. The cells in ...
1
vote
1answer
302 views

Attempting to get data sorted and counted by Calendar Quarters in Excel

I am attempting to set up a formula in excel that will look at a range of data which has dates specified in c7:c30, and categories Active-"A" and Completed-"C" in L7:L30. I am attempting to have ...
0
votes
1answer
1k views

Excel 2007: Array formula lookup using INDEX and MATCH is only looking at first row

I have a worksheet, "2011-11", with a series of numbers in column C. The numbers are each in a given currency, identified by a code in column B. What I want to do is write a single formula (in cell ...
0
votes
2answers
292 views

Can an array input be used in the sheet name of vlookup's range?

I have a formula which works, but is absurdly long, but repeats the core data over and over. My spreadsheet contains 70 pages describing sets of cards. The page I'm working on gives me a particular ...
0
votes
1answer
1k views

Use of array formulas within VBA

Excel array functions in worksheets are useful. E.g. for calculating the average of the sin values of an array A1:A5 I enter the following which works nicely: {=AVERAGE(SIN(A1:A5))} Now I would ...
1
vote
2answers
2k views

How do I lookup a 'quantity' of items in excel?

Let's say I have a quantity of items: 1 2 3 4 5 4 3 2 1 2 3 4 in a column of cells. What I want to be able to do is count the quantity how many unique "items" there are in this array: 1 ...
3
votes
1answer
3k views

Excel Range with IF

Is there a function that will create a range (from a range) if they match values? Essentially, I'm looking for something like COUNTIF, that will return the cells that actually match my IF. Ideally, ...
3
votes
1answer
1k views

Request: Excel equivalent of Google Spreadsheet ARRAYFORMULA + CONTINUE functionality

Google Spreadsheets has a syntax permitting a user to specify an entire row, column, or area with a formula in a single cell. For example, entering ... =ARRAYFORMULA(ROW(A3:A6)) ... into cell C1 ...
1
vote
2answers
140 views

Is there any documentation of the behavior of built-in Excel functions called with array arguments?

(I originally asked this on stackoverflow. However, Excel formulas definitely straddle the line between "programming" and, um, "superusing", so I believe this cross-post is appropriate.) Many of ...