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

**2**

votes

**3**answers

248 views

### Excel formula to calculate power of [closed]

Given the following equation: 2^X - 2 = N
I'd like to solve for X. N is known.
What function could I use in Excel to do this?
Thanks in advance.

**2**

votes

**2**answers

4k views

### How do I find out what's linked to my Excel Worksheet?

I've got an Excel 2007 worksheet that I inherited, and each time it opens, I get the dialog box asking if I would like to update values from an outside, linked worksheet. I'm trying to track down if I ...

**2**

votes

**3**answers

508 views

### How to get a de-duplicated list of row labels and their summed values in Excel 2010

I have an Excel sheet with multiple rows in this format:
Column A|Column B
label | value
--------|--------
header (merged cells)
labelA |12
labelB |5
labelC |9
header (merged cells)
labelA ...

**2**

votes

**3**answers

2k views

### Average of cells that match criteria and removing duplicates in Excel

I have an Excel spreadsheet where I want to remove the duplicate names and take an average for each person's score. I have a list of the following type:
Al 1
Bob 1
Bob 2
Bob 1
Joe 4
...

**2**

votes

**2**answers

712 views

### Excel: Counting the number of text strings

I have a column in a spreadsheet which lists the status of particular rows - the statuses are text strings like "accepted" "rejected" and so on.
Is there any formula I can use to count the number of ...

**2**

votes

**2**answers

5k views

### How can I change a cell's background color based on the cell's current value?

I have a column with function values of True or False:
=IF(ISNA(A1), false, true)
from row 1 to 5000+ rows. I would like to mark all the False as red background color or text as red. Is there any ...

**2**

votes

**3**answers

32k 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

22 views

### dragging to populate cells in Excel

I am aware that you can fill the cells in Excel as a series by dragging the square in the bottom right corner.
for example typing "1" in Cell "A1" and dragging the box down to "A10" will give you a ...

**2**

votes

**2**answers

3k views

### In Excel how can I sum all the numbers above the current cell?

I want to have a column in Excel that consists of a header, a bunch of numbers, and then have the sum of those numbers at the bottom. I'd like the sum to adapt to the insertion of new numbers above ...

**2**

votes

**1**answer

787 views

### SUMPRODUCT with condition

I have 2 columns B="Points" and C="Grade"
And i'm trying to find the formula to sum the products of all points*grade for grades >= 60.
I tried this formula with many others, with no success:
...

**2**

votes

**3**answers

269 views

### Robust way of creating formulas in Excel

To simplify the problem, let's say I have an Excel sheet which has different numeric values in the cells A1 to B100 (two columns by 100 rows). In column C, I would like to have a formula that will ...

**2**

votes

**4**answers

2k views

### extract date from string in excel

How can I extract date from such strings in excel?
John8/11/12
Jackson09/11/2013
Desired result:
John 8/11/12
Jackson 09/11/2013
Can I do this using only excel formula using excel ...

**2**

votes

**4**answers

4k views

### How do I calculate number of hours > 24 in Excel?

I've got an excerpt of my spreadsheet below. It's a time tracking application containing a start date/day, a session ID and the duration of the session. Column A is populated by adding the duration in ...

**2**

votes

**2**answers

2k views

### In Excel, can I provide a “default value” in case my formula returns #NA?

For example,
Say the value of a cell is:
IF(ISNA(VLOOKUP(A3,somesheet!G:J,3,FALSE) * VLOOKUP(A3,someothersheet!A:D,4,FALSE)), 0, VLOOKUP(A3,somesheet!G:J,3,FALSE) * ...

**2**

votes

**3**answers

386 views

### Update a summary sheet in Excel

I have a spreadsheet that will eventually contain 52 sheets(one for each week) On each sheet there is a cell with a percentage in it. I would like to copy this cell to a summary sheet so that it can ...

**2**

votes

**2**answers

966 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

**1**answer

938 views

### Split Address in Excel Cell

I have an Excel column where each cell contains the contents of an address. What I'd like to do is split THE FIRST TWO words into a their own separate columns.
For example:
123 South Bend Rd, Make ...

**2**

votes

**2**answers

12k views

### How do you include an OR inside a SUMPRODUCT function, in Microsoft Excel?

How do you include an OR inside the sumproduct function in Excel? I currently use two sumproduct formulas because I have two variables I want it to count:
...

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

31 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

34 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

839 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

3k 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

133 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?

**2**

votes

**4**answers

9k views

### How to add/subtract months from given date in Excel

Here is my scenario:
I have to populate list with dates and some metadata of upcoming events for the following year in my Excel application. Some of the events are occurring on the same day each ...

**2**

votes

**2**answers

270 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

6k 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

284 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

141 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

**2**answers

6k 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

5k 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

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

555 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

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

**1**answer

29 views

### Excel Help: How to scan a row and return the value of the first cell in that column

I have a matrix of numbers and the matrix has column header -- say like this:
a b c d e f
0 0 0 1 0 0
0 0 1 0 0 0
What I want to do is to search for each row for the element 1 and return its ...

**2**

votes

**2**answers

617 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

116 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

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

97 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

144 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

647 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

452 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

2k 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

90 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

206 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

256 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

100 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

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