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

learn more… | top users | synonyms (5)

0
votes
1answer
2k views

How to specify dynamic tab in a sumifs formula

I'm trying to get the below SUMIFS formula to update the worksheet it looks at depending a month referenced in some other cell, say A1. I have sales data from each month in a separate worksheet so ...
0
votes
1answer
1k views

Formula to copy a specific character from one cell to another

I am using MS query to pull in data. I need to create a column that copies the first character in another column. For example: Cell A1 - E00633 Cell A2 - B00842 Cell B1 - I want this cell to ...
0
votes
1answer
394 views

Can cell addresses (vs. cell contents) be used as operands in Excel formulae?

Consider the following scenario illustrating the data I have vs. the data I want. Very simply, I have a single column that represents Field 1, Contents of Field 1,...etc. and is more appropriately ...
1
vote
1answer
147 views

Guidelines for developing an Excel grading sheet

I'm trying to develop a convenient solution for preparation of a summary sheet comprising of lots of grading sheet underneath it. I have a tabular excel sheet used for obtaining feedback from random ...
1
vote
1answer
319 views

Complex formula in Excel

I am trying to apply a complex formula in an entire Excel spreadsheet column. What makes it complex, apart from the fact that I am not experienced in that level of Excel, is the fact that the formula ...
0
votes
1answer
209 views

Easy MS Excel - MIN column with IF condition checking another column

I'm analyzing some personal expenditures for somebody on Excel. I have a column for calendar dates and a column for expenditures on that date. Of course, there are days when there are no expenditures ...
0
votes
3answers
764 views

In excel, I need to compare the “rank” value of items in two sheets, and print the original rank in the new sheet

I've got two reports, pulled in as separate sheets in one excel file. This is the approach I came up with, but I don't know how to implement it: For each cell in column "merchant_name" in sheet 1, ...
1
vote
2answers
263 views

Subtracting percent of each cell from itself then subtracting a constant from each of those numbers and adding all of them together

I have an excel spreadsheet with several PayPal payements in a column. I want account for the paypal fee; which is 2.9%+.30cents per transaction. How do I do that?
2
votes
2answers
529 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 ...
3
votes
1answer
734 views

Is there a way in Excel to combine the contents of multiple cells and still leave line breaks?

Consider: +---+----------------------+ | | A | +---+----------------------+ | 1 | Instructions to foo | +---+----------------------+ | 2 | 1.) Foo ...
0
votes
1answer
2k views

Excel formula sorting data by date but group by column value

Morning people I've been roped into helping someone with an excel sorting issue and I've got myself a bit stuck to be honest. I need to sort a spreadsheet by date (oldest to the newest) but group ...
0
votes
2answers
457 views

Excel formula for summing columns based on a variable

I have created the below spreadsheet to track outstanding bills for clients. I am trying to create a formula that will sum all the clients who have "No" for the value in the Paid column so I can ...
2
votes
4answers
5k 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 ...
-1
votes
2answers
8k views

How do I split single cell contents into multiple columns without seperators?

I have an excel spreadsheet that has the following: Cell A1 ABCD 1234 I would like to split this to be CELL A1: ABCD CELL B1: A CELL C1: B CELL D1: C CELL E1: D I tried using the ...
1
vote
1answer
1k views

How do I set up Simpson's rule in Excel?

I know there are example spreadsheets online, but I look at the formulas and don't quite understand what is going on. I know the formula for Simpson's Rule but I don't know how to implement it in ...
3
votes
1answer
1k views

Sample data to create Excel timeline

I am trying to create an Excel timeline like the one shown here. I am not understanding how the sample data would look and how to go about making this. Please post a screenshot or explain how to ...
1
vote
1answer
2k 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: ...
2
votes
4answers
413 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 ...
0
votes
1answer
1k views

Compare two excel sheets with almost identical data and come out with matched and mismatched data

There are two excel sheets with almost similar data. One sheet is huge with around 1800 rows. and the second one is a small with around 100 rows. And these 100 rows may be present or not , if present ...
0
votes
1answer
413 views

Spreadsheet fill down for a formula

I want to add a formula to multiple cells in a column without typing them all out. now don't just tell me about the fill option as that does not do what I want (as far as I can tell). When I used ...
4
votes
2answers
3k views

Excel - concatenating cells where reference number matches [duplicate]

I have a worksheet with many rows. Some of the rows share reference numbers (contained in, say, column A). What I'd like to do is, where a row shares a reference number with another row, I'd like to ...
1
vote
2answers
20k views

Looking-up the values of one worksheet in another worksheet

Basically, I could do this manually, but as the data is huge, it would take a lot of time. I am sure there must be a formulaic way of doing it. I have two worksheets in Excel. Worksheet 1. Column-A ...
0
votes
2answers
67 views

How do I select the correct interest rate from a table of historical interest rates in Excel?

I have two worksheets in my workbook. The first worksheet keeps track of the value of my mortgage/loan. The first column contains a date, the second contains the interest rate that pertains to that ...
0
votes
0answers
392 views

Complicated Formula Using Multiple Inputs

Some really good threads here but couldn't find what I am looking for. My formula is very complex on 1 spreadsheet (sheet). There are 6 variables to input that eventually calculates a single answer. ...
1
vote
1answer
107 views

Excel Lookup Not returning Proper Result

I have a simple lookup function, which I am using in place of a Switch Case statement. Following is what I am trying to accomplish: switch(F5){ case "apple": return "1"; break; ...
0
votes
1answer
203 views

Excel date formatting after formula calculation

Say I have the following two dates in column 1 of my spreadsheet: Tuesday, February 12, 2013 10:13 AM 2/12/2013 10:13 When I highlight the column and change the cell formatting to a date type of ...
1
vote
3answers
87 views

Complex Summing Values in a Cell

Let's say I have formula result that returns this +10+5+1+40, as text. I want to then sum the those numbers to get the total. The result could be any number of values, so like +2+5 or +10+5+7+1+8. ...
1
vote
1answer
399 views

Excel formula for extracting prefixes

I have a table of 2 columns - file name & PC ID. What I need is list of all 3-letter prefixes - for each PC ID separate. For example: For: BUSstop1 PC1 BUSstop3 PC2 TRMstop8 PC1 BIKnet1 PC1 ...
7
votes
1answer
5k views

How do I copy the results of a calculation without copying the formula behind it? Open Office Calc

How do I copy and paste only the results of a calculation without copying the formula behind it? I'm learning to use Open Office Calc. I remember other spreadsheets that had a values function, or ...
4
votes
2answers
1k views

Is there a way to express a complex Excel formula in terms of final (non-formula, value-only) cells?

I have a very complicated Excel spreadsheet (formulas accessing cells with other formulas across multiple worksheets) that in the end computes a single cell (output) with input from several other ...
1
vote
1answer
1k views

LibreOffice/Excel: SUM an adjacent cell if the value in the conditional cell is equal to N (SUMIF(?))

Disclaimer: I have no idea what I'm doing. In LibreOffice Calc (3.6.2.2), I'm trying to SUM all of the values in G:x (where range is G2:G529), where B:x is equal to a given number N. The data set ...
1
vote
2answers
6k views

Excel formula to get time difference as a decimal number

I have a table like this (the C column is blank): A B C 1 19:30 23:00 (3.50) 2 14:15 18:30 (4.25) I need to calculate the time difference in each ...
3
votes
1answer
527 views

Excel: Why is VLOOKUP returning inconsistent and unexpected results?

I'm automating an Excel column to assign account values based on the vendor paid. For instance, if AT&T gets paid, the account value is Utilities:Telephone, and when the post office is paid, the ...
0
votes
3answers
14k views

Excel how to SUM a column filled with Yes's and No's

I'm not summing numbers, but rather the number of occurrences of a keyword. How is that done? Sample Data: Item Completed --------------- a No b No c Yes d Yes e No Total ...
2
votes
2answers
466 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 ...
4
votes
3answers
375 views

Suggestions for building and managing complex functions in Excel

Far too often, I've got a formula that wraps functions inside of functions inside of functions, and when some new condition requires that I wrap another function around it, I often find myself ...
0
votes
1answer
36 views

How does one add all values that occur before a specific date?

I know I need the SUMIF function, but I can't figure out the condition for the life of me. Given the following table, how would I get $150? Value | Date ------+--------- $100 | 1/1/2013 $50 | ...
0
votes
2answers
655 views

If / Else logic involving text i Excel

I want to perform the following logic in a Microsoft Excel 2010 worksheet: In cell E3: if C3 = Off or off then VALUE=0, else Value=C3*D3 Where cell C3 will contain either a number or the word 'off' ...
1
vote
2answers
3k views

How does Excel's VLOOKUP handle multi-word lookup values?

I have a worksheet that's returning unexpected VLOOKUP results, and I'm trying to narrow down the issue. I've run into a question I can't answer in my search for the solution. Consider: I have a ...
2
votes
1answer
3k 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 ...
8
votes
4answers
28k views

Is there an Excel function to create a hash value?

I'm working with a number of data lists that are keyed by document name. The document names, while very descriptive, are quite cumbersome if I need to view them on (up to 256 bytes is a lot of real ...
0
votes
1answer
190 views

Extracting delimited substring in Excel

I started with this question, but it doesn't quite address my needs. I have a series of filenames in a worksheet. Some of the filenames have cross-references enclosed in square brackets. Consider Foul ...
0
votes
1answer
83 views

how to show the result of a search as a sum in excel

I have a list of sales in an Excel file with vendor name and article price. This list is on one page, now I want to display the sum of all the sales by one person on a second page. I have tried to ...
1
vote
2answers
4k views

Match up date and time values in different Excel sheets

I have an excel file and need the following. There are two sheets in one Excel file. The first sheet shows date and time values in column C & D. The second sheet does this too in column A & B. ...
-2
votes
1answer
141 views

Count in a column the number of cells in which the first character is a letter [closed]

I have a column of data where most cells have first character "," however I want to include in a countifs formula an argumant which includes counting of cells that begin with a letter.
1
vote
3answers
3k views

Display multiple cells if data on another workbook contains “X”value

I export a complex report daily and I transfer that information manually into another excel sheet. My question is: In workbook 1, can I get a group of cells in a row to display the text in workbook 2 ...
2
votes
1answer
268 views

Excel =+ formula impact

I can't for the life of me remember but what does putting + after = in a formula actually do? For example the difference between: =+VLookup(A1,B1:B10,1,FALSE) Compared with: ...
0
votes
1answer
298 views

Excel, Average dates between transactions?

I have an inventory data set that I am playing with and I have hit a wall in trying to figure out something that I want to do. The data set has a name column (name of item; e.g., “hammer” or “saw”), ...
0
votes
3answers
497 views

Excel - Sum First Value Of Non-Unique Adjacent Column

I have a table: ColA | ColB 1 1 2 1 2 1 3 1 4 1 5 1 I would like to sum ColB only once where ColA = 2. Essentially, I would like to sum the values ...
1
vote
0answers
313 views

What is the best way to create charts of “database-style” tables and data in Excel?

I'm a biologist and I frequently have to do analysis on relatively large (for Excel) datasets. Usually a few charts are needed to display the data, but creating them seems to require laying out out ...