A worksheet function is a function or formula that is used within the spreadsheet of a spreadsheet program like excel or openoffice-calc. The function can be a user-defined function via vba or a program-ready function like =sum() or =vlookup(). These functions are used to perform simple or complex ...
0
votes
3answers
39 views
How can I compare Excel serial dates WITHOUT converting to mm/dd/yy type dates?
I have a table that contains a number of values representing Excel serial dates. After a number of unsuccessful attempts to compare fields, my current approach is to do comparisons between serial ...
2
votes
2answers
62 views
How do I `SUM` by multiple columns in Excel
I have a comma delimited file that includes two columns date/time (which imports as Excel's mm/dd/yyyy hh:mm custom format) and status of 1 or 0. The status represents a piece of equipment either ...
2
votes
3answers
37 views
How to sum up numbers conditionally in Excel?
I have the following Excel document, which tracks hours needed to implement various tasks by the development language (c#, sql, vb6):
In the cell E8, I'd like to calculate the total amount of hours ...
0
votes
1answer
27 views
Calculate age in days/months/years in OpenOffice
In need to find the age in days - months - years in OpenOffice.
There is DATEDIF() in Microsoft Excel. You can use it to find the difference in days/months/years between two dates.
Age Calculation
...
2
votes
2answers
36 views
Excel - define a formula in one cell to be used in other cells with different inputs like a function
I use excel 2010.
I want to use a formula defined in one cell as if it was a user-defined function.
Example:
In A1 I put something like (I know it's not a real syntax):
= 3 * Input + 15
And then ...
1
vote
2answers
59 views
excel formula to calculate average of consecutive 24 hour blocks?
Microsoft Excel 2010, input file of thousands of rows of hourly data, thus there are 24 values (rows) per day.
Let's assume that my data starts at B1, and let's assume that I put my average value for ...
2
votes
0answers
34 views
Excel: Exporting Formula to other languages?
I am currently using excel to simulate some hardware control logic for a processor (for a class). I am only using four formulae: If(), Or(), And() and string operations MID(). I am trying to convert ...
1
vote
2answers
69 views
When drag-copying formula cells to the right, table-references are changed
I have a simple Excel table containing a formula that is referencing columns in another table, and is using the automatic table-range-names (not sure what they're correctly called)
=A1 + ...
0
votes
1answer
27 views
How to find specific phrases in unspecified locations and add to new column on MS Excel
Sorry if the questions is not very explanatory. Basically I have a large sheet of data on prospect clients. The data includes their email addresses, however they're sparse and not in set locations ...
0
votes
0answers
22 views
Excel - How do I extract a cell content found under an argument in a dynamic table?
I've been looking a lot to solve my problem but still can't find a solution. Here's what I'm trying to do:
In the 1st sheet, I have a dynamic table containing account specs. on clients such as:
...
4
votes
3answers
76 views
Only show a formula result if it meets given criteria
I like to keep my Excel spreadsheets fairly clean. So, often times I'll end up writing statements like this:
IF([formula x]=[value],"",[formula x])
What that essentially says is that if the ...
0
votes
3answers
105 views
Formula to return value from one column when condition is met for the first time in another
Example:
Given a spreadsheet with four columns - A, B, C, and D, and row 1 is a header row...
A2=1
B2=(Random positive integer)
C2=(Another random positive integer)
A3=A2+1
B3=B2-C2
A4=A3+1
...
0
votes
3answers
81 views
Make numbered list with update feature?
Is it possible to do something like this in Excel? Like a list of active rows with an ordinal number in them.
There is a column B. It needs to show an ordinal number which represents the active row ...
1
vote
1answer
37 views
Excel: Referencing from a previous worksheet
I want to sum all the numbers from B75 to DY75 from my worksheet "Sheet1" and add that to the sum of all my numbers from B74 to DY74 in my current worksheet "Sheet 2" to get my total average. In other ...
0
votes
2answers
98 views
Spreadsheets: How do I perform functions on a set/array described by a row of (count, value) pairs?
I have excel 2000, google docs, and the latest open office calc available to me. I'm after a solution in any of those, ideally.
I have a multiset of data described by rows of (count, value) pairs. ...
0
votes
3answers
65 views
How to easily change columns into rows in Excel?
I have a huge Excel table that I need to transform into paragraphs for a Word report, and I can't find an efficient way to do it
The source looks like this:
And I would ultimately need something ...
1
vote
0answers
30 views
Alternative, more efficient scraping method for a noncoder, than Google doc's importxml and xpath?
I've searched throughout the net for a simple solution, but it seems everyone has their own unique method (coding language) of achieving this. I'm only just beginning to learn Linux, and my coding ...
0
votes
1answer
54 views
Excel 2007: Need a count when comparing differences in two columns
I have one column that contains budgets and another that contains actuals. How can I get a count in a cell above the actuals column that will tell me how many of them are under their budgets? ...
0
votes
2answers
228 views
Find a non-case-sensitive text string within a range of cells
I've got a bit of a problem to solve in Excel, and I'm not quite sure how to go about doing it. I've done a few searches online, and haven't really found any formulas that seem to be useful. Here's ...
0
votes
1answer
285 views
Excel INDIRECT function and conditional formatting - highlighting a row
I'm having an issue with conditional formatting using the INDIRECT function. I'm doing something similar to Using INDIRECT and AND/IF for conditional formatting , but the only answer there isn't ...
0
votes
1answer
45 views
Searchable tags in Excel?
I'm building a database of spiders, organizing them by name, country, and region within that country.
The problem is that many of the spiders exist in multiple countries and regions within that ...
0
votes
1answer
50 views
Google Docs / Exel - need to know how to add formula
I'm using Google Docs / Exel and can't work out how or where to add a 'formula'.
I have a column "i" which is the number of items. I have another column "j" which is the cost of those items. Column ...
0
votes
2answers
148 views
Lookup Multiple Results for Multiple Criteria
I've got a list of parent SKUs for items I need to create in my inventory system. This list has been finely paired down to the 165 products we would like to carry. However, each one of these 165 SKUs ...
0
votes
1answer
31 views
Excel 2007 How To Reference A Seperate Cell In WS1 From A Common Cell In WS2
I have a simple file with two worksheets in. In the first worksheet (a product list) are a number of columns, including Product Code and Product Description. In the second worksheet (dispatches in ...
2
votes
1answer
96 views
Split time in arbitrary periods, EXCEL
I have a list with date and quantity of items used
IE:
2009.03.18 -1
2009.06.05 -2
2009.06.22 -1
2009.06.29 -2
2009.07.14 -1
2009.07.14 -1
2009.07.14 -2
2009.07.20 -2
2009.07.30 -1
...
0
votes
2answers
69 views
Excel: How to print unique pages with only one sheet
I am trying to create a template in MS Excel to print similar pages but with an unique ID within each printed pages (e.g. ID: 001, 002 and so forth) but printing from one sheet only.
I am creating ...
1
vote
1answer
49 views
Excel: Weighted SUM of IFs
I have a spreadsheet with a list of weights and another list based on a drop-down selection with the choices "Yes" and "No" and I need to make a weighted sum from it.
Right now I have a very nasty ...
2
votes
2answers
191 views
Excel Conditional Formatting
Highlight all non-blank entries in a column, except for the column header, that do no fall in some range.
Here's my attempt:
...
0
votes
3answers
111 views
Microsoft-excel newlines and tab
Current I use "CONCATENATE" to join string in Excel, but I want insert character newlines and tab in string joined by "CONCATENATE".
Example:
I've string below when I completed join string.
"Today ...
0
votes
2answers
108 views
Cell to Equal Value of Different Cell based on Match in Array
Need to Column C to equal the text in Column G based on the match the value in Column B.
I do have Column C matching the row in which the value is found (based on columns B and F)
Column C's Formula ...
0
votes
1answer
110 views
Excel 2010: Giving another cell value to an “x” (check) in a cell
Hopefully I'll be able to explain clearly what I would like:
I have a spreadsheet about a car pool. I have to put an "x" (check) in a cell when there are seats available to transport people, but I ...
0
votes
2answers
45 views
Finding duplicates in a column where the cells hold a formula
I have a workbook with 3 sheets. Sheet "PS", column A contains a list of player names that stays static. Each week, each person in column A sends me a name from an available list. They are allowed ...
0
votes
2answers
119 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) * ...
1
vote
1answer
70 views
Combine cells with binary values in Excel
imagine I have a couple of eight cells which all contain a binary value; so it shall be something like Cell A1 has value 0; Cell A2 has value 1; Cell A3 has value 0; etc.
What I would like to have is ...
1
vote
3answers
208 views
How do I transpose data in columns to rows?
How do I transpose data in columns to rows?
If the data is:
Joe
some city
some state
Bob
no city
no state
and I use Paste Special ยป Transpose, I get
Joe some city some state Bob ...
-1
votes
2answers
113 views
Microsoft Excel - Generating random time invtervals
I have a list of employees around 30. I need to create a simple table that will assign them a specific time that they are to be on the phone and 24 hours later excel will automatically(randomly) ...
1
vote
2answers
189 views
Count negative values within date range (month)
I have values in a table like this
Date Values
01/01 5:35
02/01 -7:08
02/01 3:45
15/01 -4:05
02/02 3:25
13/02 4:25
19/02 6:20
I would like to count by month how many 'Values' ...
1
vote
2answers
131 views
Excel get cell value which is NOT Match in a row
I would like to search C2:I2 which is the cell value NOT equal to "=No=", which is "ABC", and the formula I would like to store in A2.
I was able to look for some formula which works with MATCH(), ...
2
votes
1answer
85 views
Excel formula - getting mapping details from 2 sheets of the same work book
Please can you help me with the below excel formula?
Sheet1:
Col A = Business use case number (BUC001, BUC002...)
Col B = Business use case definition corresponding to the Business use case number
...
0
votes
2answers
95 views
How to round up number in Excel
Let's say we have an array of nubmers
$9,333.00
$5,733.00
$21,333.00
$21,833.00
How can we make them round up to
$9,500.00
$6,000.00
$25,500.00
$22,000.00
3
votes
3answers
615 views
How to combine values from multiple rows into a single row in Excel?
I have a data dump in Excel that consists of annual customer data for two different values. The data was provided with a separate row for value for every year and customer. I.e. it looks like this:
...
2
votes
2answers
358 views
Excel VBA Function for Value AND Format Lookup
I need a custom function to lookup both a cell value and copy the source format. I have a list of values in two columns. The first is of integers and the second column is the corresponding text ...
0
votes
1answer
81 views
Lookup is failing to accurately use the lookup value
I am using two Excel workbooks; one is the backend for the other. In Book 1, there are sheets sheets with a $Timeline, $ClientInfo, and $VendorInfo. Book 2 is meant to be a summary record of different ...
1
vote
1answer
91 views
MS Excel 2010 - Unprotect Grouping Controls
I have an Microsoft Excel 2010 spreadsheet that I want to share with my team on a read-only basis; I want to prevent them from modifying it, so that I keep control of changes.
It's quite large, and ...
1
vote
3answers
238 views
Microsoft Excel - Count the number of values each name has in a single column
I have a CSV file containing 100,000 rows.
Each row consists of information under a number of columns, such as "Date", "Page Accessed", "Page Type", "IP", "User Surname", "User Forename", "User ...
1
vote
1answer
193 views
Prevent Excel 2010 user from pasting formatting into cell
Is there a way to lock a cell so that a user can copy/paste a value into it, but not copy formatting to it? Locking the worksheet doesn't prevent the user from changing the formatting of the cell by ...
2
votes
3answers
72 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 ...
0
votes
3answers
93 views
Excel: Find rows with first column empty
My company uses a giant excel spreadsheet that shows which IP Addresses are being used by which computers (among other information). IP Addresses that are not in use still have a row, but the ...
1
vote
1answer
184 views
merge cells with formula skip duplicates
Our Goal is to merge the names (B) when the data in A is equal. The formula below looks at the next line (A), if it's the same, it merges data (name) from B. The formula is in C, so the result of the ...
1
vote
2answers
109 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 ...
