**4**

votes

**1**answer

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 ...

**3**

votes

**1**answer

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

**1**answer

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 ...

**3**

votes

**2**answers

52 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 ...

**2**

votes

**1**answer

1k 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 ...

**2**

votes

**1**answer

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 ...

**2**

votes

**0**answers

183 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

**2**answers

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 ...

**1**

vote

**2**answers

51 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

**1**answer

55 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

**1**answer

912 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:
...

**1**

vote

**1**answer

168 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

**1**answer

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

**1**answer

307 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 ...

**1**

vote

**1**answer

40 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:
...

**1**

vote

**1**answer

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 ...

**1**

vote

**1**answer

103 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 ...

**1**

vote

**1**answer

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 ...

**1**

vote

**2**answers

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 ...

**1**

vote

**1**answer

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

**1**answer

238 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

**1**answer

198 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

**3**answers

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:
...

**0**

votes

**1**answer

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

**1**answer

114 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 ...

**0**

votes

**2**answers

48 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

**1**answer

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

**2**answers

296 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

**1**answer

22 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

**1**answer

34 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

**1**answer

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 ...

**0**

votes

**0**answers

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

**0**answers

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 ...

**0**

votes

**1**answer

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

**1**answer

46 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

**0**answers

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

**0**answers

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 ...

**0**

votes

**1**answer

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

**1**answer

985 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 ...

**0**

votes

**1**answer

151 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 ...

**0**

votes

**1**answer

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 ...

**0**

votes

**1**answer

57 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 ...

**-1**

votes

**1**answer

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

**2**answers

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 ...