Questions about using in-cell functions/formulas in spreadsheet software.

**2**

votes

**3**answers

30k views

### How to set Function “formula” for whole column in Excel 2007?

I want to set "Formula" to all cells in certain column whatever the number of rows was.
But i don't want to set this function to first cell "Header" of this column.
How do i make it ?.

**2**

votes

**2**answers

6k views

### What's the difference between “vlookup” and “lookup” in Excel?

What is the main basic difference between VLOOKUP and LOOKUP in Excel?
I read the main point is that you can specify a range of columns in VLOOKUP and then pick one to return the value. However, it ...

**2**

votes

**2**answers

24 views

### Looking up last two entries in a column

This might be impossible but I hope not. I am trying to pull the last two entries from a column and add them together and then divide by two to get a percentage. These entries are updated daily so the ...

**2**

votes

**1**answer

32 views

### Excel 2007 nested function “if, then”

I keep getting a message that I have too many arguments. I am trying to ask: If value 1 = x, then value 2; otherwise value 3. BUT if value 3 = 0, then blank.
It doesn't like:
...

**2**

votes

**1**answer

471 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

**3**answers

2k views

### Generate a comma-separated list of cell contents, excluding blanks

To concatenate a certain four cells, I'd use:
=CONCATENATE(A2,",",C2",",D2,",",F2)
This would make it so that...
A2 = "Matthew"
C2 = "Mark"
D2 = "Luke"
F2 = "John"
would result in ...

**2**

votes

**2**answers

213 views

### How can I flag the presence/absence of a specific substring within a string in Excel?

We have a list of keywords associated with a list of documents. The list was created from frequency counts of the words in the document text. We are trying to add a weight to the keywords based on ...

**2**

votes

**1**answer

3k views

### Is it possible to solve systems of linear equations in Microsoft Excel without using VBA?

Suppose you have the following system of linear equations and want to solve for x, y, and z:
2x + y - z = -1
-4x + 11y = 18
x - y + 2z = 9
Is there an easy way to do this with Excel using ...

**2**

votes

**2**answers

2k views

### What function should I use in Excel for searching a (multiple) text string?

The title is a bit unclear, but I'll be explaining it now for better clarity.
I have this:
When I type in the Input field, I'd like Excel to show me the result in the Output field. For example, if ...

**2**

votes

**1**answer

5k views

### Excel Formula: What is the equivalent of =MATCH(), but for multiple columns?

Problem
This formula will return the row number where the value "Bob" appears. Say the value happens to be in $A$10, the formula would return: 10
=MATCH("Bob",A:A,0)
I would like a formula to do a ...

**2**

votes

**3**answers

261 views

### How to sum up numbers conditionally in Excel? [duplicate]

Possible Duplicate:
Sum Values in One Range Based on Criteria in Another
I have the following Excel document, which tracks hours needed to implement various tasks by the development ...

**2**

votes

**1**answer

123 views

### Why is Excel showing 'c' as the arg separator for fomulas?

In Excel 2007, I have a cell with
"=if(true,1,0)"
as the contents. If I hit the ENTER key
I get an error dialog which says, "The formula you typed contains an error."
The flyover help for ...

**2**

votes

**1**answer

4k views

### What excel formula can I use to calculate the 2nd Monday of a given month?

What excel formula can I use to calculate the second Monday of a given month?
You can assume I have a cell containing the first day of the month to work with.
In the interests of other users, can ...

**2**

votes

**2**answers

861 views

### How does one find the definition of a function in Excel 2010?

I've been tasked with making changes to an Excel 2010 spreadsheet I know little about. I was pretty good with Excel about a decade ago, but I'm not really sure how to look-up the definitions of ...

**2**

votes

**3**answers

1k views

### Excel notation that gives FIND the ability to look for multiple strings

So I've been working with a spreadsheet that has a list of schools names. I pull data from this spreadsheet to create a roster that limits the size of text in each cell. In order to get the school ...

**2**

votes

**1**answer

499 views

### Numbering grouped data in Excel

I have an Excel spreadsheet (2010) with data similar to this:
Dogs Brown Nice
Dogs White Nice
Dogs White Moody
Cats Black Nice
Cats Black Mean
Cats White Nice
Cats White ...

**2**

votes

**2**answers

99 views

### How to add or subtract to, or increment, column letters in Excel?

I'm looking for a simple versatile formula that allows simple mathematics to be done to column positions, returning the appropriate column letter code. For example:
Given column A, set to +1, it ...

**2**

votes

**3**answers

79 views

### How to make excel return a date from a column corresponding to the last 'YES' in a different column

I'm trying to develop a spreadsheet tool to help track the training currencies of professional aircrew. In order to be considered 'current' in all training, aviators must accomplish a variety of ...

**2**

votes

**1**answer

264 views

### How to hide formula results until conditions are met

I am completely new to making spreadsheets with Excel and even more dumb when making formulas. I have come to surprise myself how much I have learned, but now I am stumped and could really use some ...

**2**

votes

**1**answer

82 views

### Remove pseudo-blanks in Excel?

Is there a way to make supposedly blank cells truly blank?
I have had a bunch of formulae evaluate to "", whereafter I proceeded to copy the results and paste values. Now when I check for blanks ...

**2**

votes

**1**answer

103 views

### How do make an Excel cell reference a particular sheet according to the current date?

I have an Excel spreadsheet for tracking sales with the first page being for totals, then a separate page for each month's sales, which has subtotals on each page.
Can I make the "this month's total" ...

**2**

votes

**1**answer

300 views

### Is there an Excel formula that can conditionally sum rows based on column headers?

I need to be able to put a formula into the last cell of a row (shown by = in the image) that totals the values in that row that don't have a cell with the text 'Done' at the top of the relevant ...

**2**

votes

**1**answer

217 views

### Excel: Reverse VLOOKUP with comparaison

I have an original table (the value column is sorted):
name | id | value
--------+-------+-------
abc | 1 | 12
def | 2 | 15
ghi | 3 | 23
jkl | 4 | 29
...

**2**

votes

**2**answers

850 views

### Excel reference named range with text and formula

Is it possible to refer to a named range by combining text with a formula? Say I get a name for a range via INDEX which returns a text but I also want to add something after the text like so:
...

**2**

votes

**1**answer

78 views

### Filling content of cell based on adjacent cell

I have a column of data like column B below. The letter "A" denotes software, "B" a service and "C" hardware. How do I match each product with its category to make it look like the table below?
A ...

**2**

votes

**1**answer

159 views

### Excel: How do I calculate net workdays for an array of date ranges?

I have a list of date ranges, let's say:
A B
1 April 25, 2013 May 31, 2013
2 September 30, 2013 October 18, 2013
I need to calculate a sum of net working days of all ...

**2**

votes

**2**answers

167 views

### SUMIF by category and date in range

I have a table where I keep a transaction log of expenditures as so (excerpt)
Date Paid Purpose
where the date is the date when the transaction is conducted, Paid is the actual amount paid ...

**2**

votes

**1**answer

83 views

### Capitalize first word in each row of a column in mac

I want to capitalize the first letter of every word in each row of a column.
So I create a new column, type in =PROPER(B2), then drag that down the entire length of that column. It works great, it ...

**2**

votes

**3**answers

1k views

### Include an OR condition in a COUNTIF?

This question has been asked before and the answer always seems to be to add together two COUNTIFs. This is fine for most applications, but won't work for me.
I have a column that can contain 0, 1 or ...

**2**

votes

**1**answer

790 views

### Hide “inconsistent formula” error in a selection in Excel

This may seem easy to more advanced users, but I can't seem to find this out there.
I have a complex Excel worksheet. It has various formulas/calculations throughout the sheet.
Unfortunately, ...

**2**

votes

**3**answers

350 views

### How to format a date field based on whether the date has passed?

I am trying to create an Excel spreadsheet where, when I input a date:
if that date is within 30 days it will go yellow,
if the date has already gone then it goes red, and
if the date is more than ...

**2**

votes

**2**answers

296 views

### Is this the best strategy for displaying a negative time value in Excel?

I am trying to send up a calculated cell as duedate - now() in order to quantify overdue vs. time remaining (negative values = overdue, positive values = time remaining). Consider:
Col A ...

**2**

votes

**2**answers

5k views

### sum of vlookup using array formula

I have such table of payments:
account | amount
--------+-------
101 | 3
101 | 5
102 | 7
103 | 9
I named the range of this table "payments". Its first column I called "accounts". ...

**2**

votes

**1**answer

1k views

### Why would two Excel cells that appear to have the same values test as not matching?

I'm creating a validation column for a worksheet. I've attached an image of what's going on.
Column A contains an indexing function and works properly. The formula appears at the bottom of the ...

**2**

votes

**2**answers

149 views

### Get count of items based on certain dates

I need to solve this query:
Column A contains an array of dates
Column B contains daily entry of no / yes
This formula returns the number of occurrences for no:
=(COUNTIF(b1:b365,"no"))
I need ...

**2**

votes

**2**answers

78 views

### What is { } used for in Microsoft Excel 2010?

I came across this function in one of my worksheets and wanted to know what the curly braces are used for.
Here is an example.
{=array(sheet1!a1,sheet2!a3,sheet2!a5,sheet2!a6)}

**2**

votes

**1**answer

202 views

### Excel: Weighted SUM of IFs [duplicate]

Possible Duplicate:
Sum Values in One Range Based on Criteria in Another
I have a spreadsheet with a list of weights and another list based on a drop-down selection with the choices "Yes" ...

**2**

votes

**3**answers

605 views

### Advanced cell selection in Excel

I am new to this flavor of StackExchange, so if this belongs elsewhere, please move it; I figured this would be the best place, though.
I am making an Excel Worksheet that simply stores basic ...

**2**

votes

**2**answers

5k views

### Excel: How to return the last Row Number that a contains a specific string?

In excel, is there an excel formula I can use to populate Column B (in my example below) that will return the last row number that contains a specific string, before the current row?
The data is not ...

**2**

votes

**1**answer

340 views

### How to calculate AVERAGE(POWER(A1:A2, 10)) in Excel?

I can do this creating a new column B where =POWER(A1, 10) and then I get the result with =AVERAGE(B1:B2). But how to do that without a new column?

**2**

votes

**1**answer

154 views

### How to retrieve non-contiguous cell collections from strings in Excel?

I tried to =SUM(INDIRECT("B4,B6")) but it doesn't work. How to do that?

**2**

votes

**1**answer

1k views

### Assigning a weighted value to a column

I'm working on a spreadsheet in Excel. Column A is student's name. Column B is a placement test score. Column C is a benchmark score. Column D is an effort score. Column E is for a TOTAL score. ...

**2**

votes

**3**answers

10k views

### Subtracting cells only if they have a value in Excel

I want to subtract two cells in excel but only if both cells have a value. More specifiaclly in my case the formula in cell E2 is currently =D2-C2 but I only want this to be calculated when D2 has a ...

**2**

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

**2**

votes

**2**answers

2k views

### Summing a column in the same column without a circular reference

In Excel you can sum a whole column with =SUM(B:B). But of course you can't use that formula in the column you want to sum, because you'll get a circular reference.
Is there an easy way of summing ...

**2**

votes

**2**answers

3k views

### How can I write an excel formula to do row based calculations?

Could someone please help me with the following excel formula. Each row in my spreadsheet contains details about a product.
I have one column 'Quantity' (Col B) and 'Profit' (Col F). I want to use ...

**2**

votes

**2**answers

4k views

### What is an excel formula that will return me the last row number of a cell within a range that contains specific data?

I have a spreadsheet that will default with values of "No". Over time, these values will be changed to "Yes".
I need a formula (no VB, please, VB is useless to me) that will get me the row number of ...

**2**

votes

**1**answer

29 views

### When copying formulas from one workbook to another, how to make the formula to use the data from the new workbook?

OK, I know it sounds complicated, so I will try and elaborate:
I have a lot of files (one created every day) - let's call them W1, W2...
Every file has the same number of sheets and their names are ...

**2**

votes

**2**answers

59 views

### How to deal with lots of brackets in a formula?

Say, I have a formula like this (in LaTeX or Maple or other text system):
Result: ...

**2**

votes

**2**answers

79 views

### Excel: searching for a maximum lower than X

From the sorted list of values, I'm looking for a maximum that is lower than X
Example:
A B C
1 1 X=7
2 3
3 5
4 8 ANS=5
Do you have any smart ideas for this problem?