Questions about using in-cell functions/formulas in spreadsheet software.
4
votes
2answers
61 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
18 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
96 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' ...
2
votes
1answer
124 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 ...
0
votes
0answers
31 views
Excel function is not returning expected results
I have a list of documents, and I'm trying to flag those that have the word see in the title. The formula I'm using is =--(ISNUMBER(SEARCH("see",F43))). but all my results return as 0.
ACE [see C3] ...
1
vote
2answers
972 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
82 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
52 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 ...
0
votes
2answers
199 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
43 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
2answers
157 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 ...
0
votes
1answer
103 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
105 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 ...
0
votes
0answers
70 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 ...
2
votes
1answer
71 views
Is there any way to programmatically stylize part of a string in a calculated cell in Excel 2010?
Let me explain what I mean.
I have a cell that contains something like this:
="String of text that's moderately long" & YEAR(NOW()) & "."
And I want to know if there's any way that, via ...
1
vote
2answers
194 views
Excel timesheet. Public holiday pay conditional on precise conditions being met
I'm trying to make an Excel timesheet that does some quite particular functions.
1) If we say Wednesday is a public holiday, if the employee worked on Tuesday AND Thursday (days on either side of the ...
1
vote
7answers
81 views
Rounding in Excel
I have a list of numbers in column A that go to several decimal places (e.g. A1 is 27.34). Is there a way to fill column B with the same values as column A, but rounding to the nearest integer?
0
votes
1answer
66 views
Split N*Y rows of data into Y columns of N rows
My question is basically the same as this one, but the answer given for that question doesn't work when I try it (see image), and I don't understand the syntax of the function well enough to ...
2
votes
2answers
256 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". ...
0
votes
2answers
91 views
Excel. Double the number in cell A7, IF all cells from A1 through A6 contain a value higher than 0
Excel. Double the number in cell A7, IF all cells from A1 through A6 contain a value higher than 0.
I am trying to make an excel timesheet where Employees who work on Sunday get paid at double their ...
0
votes
1answer
108 views
Libreoffice Calc SUM returns ###
In Libreoffice Calc, sometimes the SUM function returns ### in the cell when referencing other sums. For example:
=SUM(x1+y1)
where
x1=sum(a1:d1)
y1=sum(a2:d2)
gives
###
in the output cell. ...
1
vote
4answers
62 views
Vlookup using wildcards in indexed column
I know how to use a wildcard with Vlookup on the reference value, but what about on the matched column index?
I know you can do for instance VLOOKUP("*Hello*",A4:G4,2,FALSE)
However, what if you ...
5
votes
1answer
53 views
What should I do if I see a reference in Excel cell formulas to a sheet that I cannot see in the workbook?
Similar to 'Reference Data' in the publicly accessible file described below.
There is the Capacity Planning Guide spreadsheet excel file on Capacity Planning Considerations for Windows Azure Caching, ...
2
votes
1answer
74 views
Merging Excel files on a keyfield
Using the answer to this question as my starting point, I have a slightly different scenario that I'm stuck on.
Consider:
File 1: Incident# (key), foo1, foo2, foo3
File 2: Incident# (key), status, ...
4
votes
1answer
187 views
Issue in Excel: Copy a cell with formula - pastes value of the copied cell
I have the following copy and paste issue since yesterday. Probably it started when I was trying to edit a locked Excel-doc which i found on a shared server (that is - it was not created with my ...
0
votes
1answer
70 views
Can I force MS Excel table column to use the specified pattern?
I need to develop a tool that enforces strict format of some table. The fields in this table follow specified patterns like number:number (11:20. 2:00), agreed date (11/20/2012 but not ...
0
votes
2answers
239 views
How to find specific RANGE ADDRESS from array based on value from the first column?
It's been a long time since my last work on Excel formulae and I'm having problems with =INDEX and =MATCH. I have this array:
A B C D E
app
csd
arg
egd
and from there, I would like to ...
0
votes
1answer
88 views
How to achieve something like this in Excel?
So I have a table full of data which looks like this:
Subject A Subject B Subject C Subject D Count
Person A F F F 3
Person B F ...
0
votes
2answers
330 views
Count how many times a text appears in Excel 2010
How do I count how many times each text appears in a column?
For example I have this column:
foo1
foo1
foo1
too2
foo1
too2
mmm
mmm
foo1
Now I like to excute a function that shows me this result:
...
2
votes
2answers
212 views
Excel method to find the values closest to a given period
I have some data measured at non uniform time intervals, for example:
Time(s) Value(KB)
2.1 304
43.0 400
60.2 380
87.0 440
89.5 445
102.2 460
...
0
votes
1answer
245 views
How to search a string in many workbooks and copy row if true
I need to run a report for a customer.
I have approximately 50 files (Excel 2007 workbooks) in a folder. Each workbook has about a hundred rows and ten colums. I need to search a string (in a known ...
1
vote
4answers
148 views
In Excel, is there a less repetitive way of writing `=IF(<Expression A>=0, “(none)”, <Expression A>)`?
In Excel, I have a long, complex expression (Expression A) that I am using in a formula. I want this formula to return the value of Expression A except in the case where that expression is equal to ...
2
votes
3answers
138 views
In Excel, can you define a cell or range to be used in a formula based on the contents of another cell or formula?
What I want to do is this:
Have one cell calculate the number of items present in a particular column, e.g. C1 = 129
Have a second cell use that number as part of the definition, e.g. D1 = ...
0
votes
1answer
121 views
Need to VLOOKUP in worksheet in the same workbook based on cell value
I am a building a workbook (spreadsheet) that has worksheets labeled by month; e.g., "Jan-12", "Dec-12", and so on. I need VLOOKUP to be able to change where it looks based on the name of any given ...
0
votes
3answers
84 views
Calculating a sum on an integer column when another column includes the year criteria for the calculation
I have a spreadsheet where the columns contain values as follows:
Column(A) B C D E
NameA 01/02/2010 5 4 5
NameB 05/06/2011 4 3 ...
1
vote
1answer
65 views
Exporting an SC query result to Excel
I wrote an SC query to show all the services and their state and I can easily display it in a text file. What I want to do is list all this data directly to an Excel worksheet.
How / is this ...
0
votes
3answers
105 views
Finding multiple coded values in a string, then returning a lookup value for each found code
I have 600 item codes, and each one is coded with attributes in the overall item code. For example, we might have the plain item 600, then 600BK (Black body) and 600BKR(Black Body, Red colored detail) ...
1
vote
2answers
68 views
How to pass a formula as one of the arguments to CONCATENATE?
I'm using Microsoft Excel 2010 and I am trying to create a title which will be updated every time the spreadsheet is opened. I want it to say "Forecast from (today's date) until end of 2013".
So far ...
1
vote
1answer
78 views
Choose maximum from two Excel cells
We have two pricing models for an item.
Price 1 = Cell 1
Price 2 = Cell 2
Is there a formula that I can use where the price for the customer would be the whichever is higher among Price 1 and ...
0
votes
3answers
141 views
Microsoft excel formula on rounding up values
If a value is less than 200, then it should be rounded up to 200.
How do you do this in Microsoft Excel?
0
votes
1answer
113 views
excel 2010 open dialog window after changing formula
I have to copy 120 thousand formulas from one xls file to another. After a simple copy-paste, the original file name is inserted into the formula, not good. I tried to remove it with Data - Links, but ...
1
vote
1answer
171 views
Numbers conditional formatting based on a different cell in the same row
I have a Numbers spreadsheet, with "quantity required" and "quantity in stock" columns, then each part is on a row. I'm trying to use conditional formatting to turn a cell yellow when # required = # ...
2
votes
2answers
80 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 ...
0
votes
2answers
602 views
Sum of vlookup values on multiple sheets
Have 12 excel Sheets in a workbook for each month Jan-Dec. Each sheet contains Names and leaves marked for that month in the same row and sum value for each category of leave at the end of the table. ...
1
vote
1answer
461 views
How to calculate a percentage of completed task in this spreadsheet?
I have the following spreadsheet. Basically Sam and Heather split out their assignment by task. When the task is complete, they mark it with Y in the Task Done column.
At the bottom, in yellow, I ...
0
votes
1answer
107 views
Formatting Time Entries in Excel 2010
I have an Excel Spreadsheet. Essentially, I'm trying to use this spreadsheet for time tracking purposes. I have the following columns:
Date (Formatted as a Date in Column A)
Start Time (Formatted as ...
0
votes
0answers
13 views
Editing a protected Excel sheet when macros and VB are disabled [duplicate]
Possible Duplicate:
Remove password from an Excel Document
I have a password-protected Excel file which I need to edit but cannot because I don’t have the password and macros are disabled ...
0
votes
1answer
161 views
Changing cell values based on another cell
Lets say that I have 3 parameters/cells:
High (Cell A1)
Medium (Cell A2)
Low (Cell A3)
the fixed values for these cells are 2, 2 ,2
how can I change the value of cellA1 based on the changes of ...
1
vote
2answers
201 views
Comparing Two Columns in Excel, Returning a Third
I've hit a bit of a brick wall in my Excel-ing. Below is the example and explanation.
Sheet 1:
Column A | Column B | Column C | Column D
_ Name _ | _ Date ._ | _ Cost .._ | _ Code _
...
1
vote
2answers
103 views
Does AND work in Excel spreadsheet formulae using Ctrl-Shift-Enter?
I have an Excel (2010) cell with this formula:
{=SUM(IF('Tasks'!$I:$I=$C4,'Tasks'!$F$2:$F$2000,0))}
which works (the correct total is given). But when I enter another item to search for:
...
