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

learn more… | top users | synonyms (3)

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