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

learn more… | top users | synonyms (5)

0
votes
2answers
608 views

Formula help for matching data from multiple columns in multiple worksheets

What formula do I need in my worksheet DFW column C that will do the following?: On worksheet DFW take data from cells A2 together with B2 and see if there is an exact match in worksheet Feb columns ...
0
votes
1answer
247 views

Get unique comma separated values from list of comma seperated values

I have a list of csv in a single cell, and I need to get the unique values from that list and put them in another cell. For example, in one cell I would have: DIV-154, FOD-371, UDL-913, DIV-154 And ...
1
vote
2answers
2k views

How can I filter specific rows from an Excel spreadsheet based on criteria?

I have a spreadsheet from which I need to select ONLY the first five rows in each category. I only want columns A and B when C is less than 5 -- =IF($C1>5,$A1,) and =IF($C1>5,$B1,) -- but I ...
0
votes
2answers
515 views

Sumproduct fails when range is only 1 row and part of the calculation is blank

I have the following formula that works when the ranges have more than 1 row, but generates a #VALUE error if there is only 1 row and one of the factors is blank. =SUMPRODUCT(cost, units) Cost ...
0
votes
1answer
537 views

Unable to get the VLookup property of the WorksheetFunction class

I want to implement a barcode finder in excel, so I have item names and barcodes in separate sheets depending on what group an item belongs to. The user clicks on a radio button (selecting Group 1, ...
0
votes
1answer
157 views

Complex excel formula help needed?

I have a complex formula i need to create to look up various fields, with percentages of totals to be given if certain criteria are met, and need some guidance. In column P i have a monetary figure. ...
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
953 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
354 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
141 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
277 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
186 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
596 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
195 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
445 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
580 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
1k 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
401 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
4k 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 ...
0
votes
1answer
5k 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
928 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
1k 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
3answers
329 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
316 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
2k 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 ...
0
votes
2answers
12k 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
64 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
300 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
101 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
156 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
82 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
289 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 ...
5
votes
1answer
4k 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 ...
0
votes
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
3k 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 ...
2
votes
0answers
406 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
8k 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
359 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
304 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
28 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
561 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 ...
7
votes
4answers
20k 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
167 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
76 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
3k 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. ...