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

learn more… | top users | synonyms (3)

2
votes
3answers
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
2answers
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
3answers
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
3answers
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
2answers
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
2answers
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
3answers
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
2answers
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
2answers
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
1answer
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
3answers
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
4answers
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
4answers
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
2answers
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
3answers
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
2answers
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
1answer
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
2answers
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
2answers
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
2answers
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
1answer
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
1answer
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
3answers
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
2answers
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
4answers
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
2answers
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
1answer
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
2answers
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
1answer
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
3answers
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
1answer
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
2answers
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
1answer
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
3answers
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
1answer
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
2answers
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
1answer
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
2answers
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
3answers
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
1answer
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
1answer
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
1answer
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
1answer
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
1answer
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
2answers
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
1answer
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
1answer
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
2answers
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
1answer
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
3answers
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 ...