Questions about using in-cell functions/formulas in spreadsheet software.
1
vote
3answers
124 views
Counting frequency of comma-delimited tags in Excel
I have an excel spreadsheet with a list of seminars and classes that students have taken that (when abridged) looks like:
(A)___ClassName________|(B)_________Tags____________|
Astrobiology ...
0
votes
1answer
44 views
VLOOKUP in Excel with IF statements?
I'm not sure whether IF statements are actually needed in this problem;
however, I'm totally stumped.
In the first worksheet I have a column for "years employed" (Column D6:D107) ranging from 0.1 to ...
0
votes
5answers
75 views
Excel - Sum of values in a column given the order type available in another column
I have excel 2010 and I am trying to do the following (the table below has been done manually)
Customer Order type Revenue Sum
001 Pears 50
...
0
votes
2answers
63 views
Auto orderlist Excel 2010
I'm using Excel 2010 to create a inventory of my shop.
Now I would like it to automatically create a order list in the second sheet.
My first sheet look like this.
Item: Stock: Min. Max.
...
0
votes
2answers
69 views
Copy multiple cells from one to multiple worksheets
I have a workbook in Excel 2007 with 52 worksheet each representing a week of the year, each worksheet is laid out the same with 16 rows and 26 columns to give me 14 personnel and 24 hours with other ...
0
votes
1answer
111 views
How can I get the hex value of an 8 row column with depending on background color?
I have a spreadsheet that I'm using to help visualize a character bit map for each ASCII character. For a 5x7 font, I use 5 cells wide (columns) and 7 cells tall (rows). I then marked the background ...
2
votes
1answer
200 views
Compare multiple columns (more than 2)
Okay, so let's say I have a chart with N columns of data. What I want to do is compare every column to every other column, and get a count of how many values are equal in both columns. I know I can ...
2
votes
2answers
70 views
OpenOffice Calc regex returns wrong value - what's wrong with my regex?
I have a column of text with values concatenated by my bank, as in a typical monthly statement.
Here is an example (column A240):
OCT 26 BALANCEPROTECTOR PREMIUM $10.05
I'm trying to set the ...
0
votes
1answer
68 views
creating multiple plot per level of factor (trillis graph) in excel
Is there way to automate the process to make graphs like the following (also known as trallis graph) in excel ? I know there are some R interface with Excel (some of them need to be purchased), but I ...
1
vote
1answer
75 views
How to find last and first value meeting criteria in a series of values?
This is a sample of my input:
Then, I have midnights in a consequent days and I would like to achieve this result automatically:
To put it in words: I have datetimes in one column and I would ...
0
votes
1answer
137 views
Excel Formula for filtering data from one sheet to another
I have table that have the following columns (Agent Name, Client, Policy #, Premium) with the name of agents repeating several times since they have multiple clients...
I don't want to give all the ...
-2
votes
2answers
55 views
MS Excel IF THEN formula
I am trying to write a formula such that, if T5 is 1, and AB5 is 1, then LOW;
OR if T5 is 1, and AB5 is 2, then HIGH;
OR if T5 is 1, and AB5 is 3, then EMERGENCY
etc., for the whole chart..... T5 is ...
0
votes
2answers
94 views
Modify formula to work with conditional values
I am currently using the below formula to calculate enablement costs for meters
=SUM(SUMIF($D74,{"New Business"}),$U74*5500)
This worked fine as the fee of $5500 was standard, however I now need to ...
0
votes
2answers
106 views
How can I turn a Calculated Column back into a data column in an Excel Table?
I have a large Excel Table (44 columns, 1000+ rows), but one of the columns has been accidentally turned into a Calculated Column (See Here). The result being that now when the users try to add new ...
0
votes
3answers
102 views
change link cell formula across sheets
I have a workbook named "stu", it has 1 to 30 sheets. In cell H4 I have linked a formula:
=[sub.xlsx]Sheet1!$B$2
in sheet1: cell H4 the formula in cell H4 should be =[sub.xlsx]Sheet1!$B$2
in ...
1
vote
2answers
82 views
How is OFFSET supposed to work on filtered EXCEL data?
Consider:
+---------------+
| Column A |
+---+---------------+
| 1 | Milan |
+---+---------------+
| 2 | Paris |
+---+---------------+
| 3 | London |
...
0
votes
2answers
66 views
Excel formula to search cell values from one column and add values from another cell if true
I don't think this should be too complicated for Excel, but I am trying to figure out how to:
1) Determine if a cell in column A contains the text "FF"
2) If so, jump over to the corresponding cell in ...
0
votes
3answers
203 views
show cell value if another cell is true, else go to next row
It's my table in Sheet1:
A B C D
------------------------------------------
1 220 John Good
2 210 Jack Bad
3 300 ...
0
votes
1answer
55 views
Ms Excel Formulas for Date and Day
I need help.
I have written a specific date, for eg 13/03/2013 in A1.
Now I want its corresponding weekday in A2 (i.e. 13/03/2013 (Wednesday).
In A3 I want it to show two days ahead (i.e. 15/03/2013).
...
2
votes
3answers
104 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 ...
1
vote
3answers
81 views
How to create an excel report by month
I have a list of data in excel with a date column. When I want to generate a report I typically create a new column in the data tab and type in a code in text format like "April08", then I put a ...
0
votes
1answer
103 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
78 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. Probably easier to see than describe....
I only want columns A and B when C is less than 5 -- ...
0
votes
2answers
118 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
68 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
78 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
64 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
49 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
72 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
144 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
81 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
51 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
40 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?
1
vote
0answers
66 views
Excel 2007 - Chart Not plotting/graphing new/additional series
I have workbook with many monthly worksheets (one year) that "build" on each other. Some data becomes cumulative each month. Since 90% of the worksheet is duplicated, I copy the worksheet to the next ...
2
votes
1answer
130 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
75 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
166 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
58 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
127 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
220 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
328 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
301 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
3answers
43 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
139 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 ...
3
votes
2answers
269 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
123 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
40 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
98 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
39 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
42 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 ...
