Questions about using in-cell functions/formulas in spreadsheet software.
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
163 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
203 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:
...
1
vote
1answer
244 views
Is there a way to delete everything except what is selected in Excel?
I have an Excel template with 20 tabs (worksheets) and plenty of data in each sheet. When a user opens a copy of the template, he will only need to use one tab.
Is there a sneaky way to select that ...
0
votes
0answers
88 views
Creating a Macro or Formula to copy data through a query
Here is my formula that I'm looking to build off of:
=IFERROR(VLOOKUP($A1,Table2,10,FALSE),"")
I don't know if I can use just a formula or if it has to be a macro. I am trying to create a query ...
1
vote
2answers
147 views
Excel: Return last positive value on row, and another value related to it
I have a sheet on which values relating to website URL redirect chains are returned. At the end of each row, I want to return the final URL and its HTTP response. These values are populated by ...
2
votes
1answer
258 views
excel chart dynamic range based on values
I'm trying to create a chart that auto updates itself from a data provided. The range of the chart is always fixed/locked. The issue I'm facing is that when a value for a certain month is 0, I want it ...
0
votes
2answers
80 views
Entering text value representing a sum in Microsoft Excel
Please see this image:
Under "Monday" where I have typed rent, I would like that cell to display the text "Rent" still, but to represent the value of rent on row 4. For the formula in "balance" ...
0
votes
1answer
72 views
How do you find a difference among cells in a single column?
I have a list of ~100 values in a column. I want to find all possible differences among cells of a value of 30 +/- 2. For example, 1200, 1201, 1205, 1208, 1230. The difference between values 1 and 5 ...
0
votes
2answers
220 views
Excel: Check if column A has any values from Column B, and delete that value from Column A
So I have two columns in Excel with these values suppose:
Column A | Column B
---------|---------
12 | 626
241 | 129
35 | 412
412 | 88
532 | 663
626 |
723 |
88 ...
2
votes
1answer
476 views
Is it possible to solve systems of linear equations in Microsoft Excel without using VBA?
Suppose you have the following system of linear equations and want to solve for x, y, and z:
2x + y - z = -1
-4x + 11y = 18
x - y + 2z = 9
Is there an easy way to do this with Excel using ...
1
vote
1answer
69 views
excel index match do not ignore 0's
I have a function =INDEX(Database!E$5:E$50,MATCH(TRUE,INDEX((Database!E$5:E$50<>0),0),0))
that searches a column, finds the first number and returns it. Right now it is ignoring cells in the ...
0
votes
2answers
85 views
Filter Duplicates by count of a character Excel 2007
I have a data list of around 32000 items in column A. Each of these items has many duplicates, some having 1 duplicate, up to about 5 or so max. Each cell has multiple sku's, divided by a /.
What I ...
0
votes
1answer
201 views
Compare a column from two different sheets and return true/false
I need to compare a column from two different Excel sheets and return a value of true or false.
0
votes
1answer
54 views
Get frequency distribution of several columns partitioned by one other column
I'm doing some basic survey analysis. I want to break down responses to various questions based on Male or Female. Each row is a response, each column is a question. Column A has the Male/Female ...
0
votes
1answer
117 views
excel function searching multiple rows
I have a worksheet with a column (A) with dates. Some rows between dates are blank. I want to put a cell on another sheet that searches sheet1, column A for a particular date, and when that date is ...
1
vote
3answers
206 views
How do I replace cell values from column A to column B in Excel 2012?
I want to find all cell values in column 'A' with, let's say a value "gmail.com" and replace all values of column B with "EN" (change the value in the same row of A and B result).
Is there a function ...
0
votes
1answer
125 views
“IF” condition to compare two sheets
I want to compare two sheets and write results to a third sheet. The first sheet has two columns:
title_add | add something
title_del | del something
title_lst | list something
The second sheet: ...
0
votes
2answers
110 views
Conditional formatting with date and time
I have a problem on conditional formatting with date and time.
I have a cell A1 that has date and time and I want to conditionally format its adjacent cell if the value of cell A1 is greater than 3 ...
1
vote
1answer
48 views
Formula for Formatting Date in Excel
I need to make a formula that will reformat a date in excel. My content is as below:
Cell E4: 2/15/2012
I would like column E5 to be like this:
Cell E5: 15 February 2012
Is there an easy way ...
0
votes
1answer
58 views
HLOOKUP Return Greatest Value
I have some columns A, B, C, D, E, F, G with some values in the rows below 2, 3, 4. I don't know if HLOOKUP is the correct answer for what I am trying to do but I want to have a function that looks at ...
0
votes
1answer
84 views
Creating Crosstabs in Excel
I have a survey asking about mobile device usage, but people have the option of checking more than one device...so folks may check iPad and Android phone, or folks can only check iPhone, etc.
I am ...
1
vote
1answer
139 views
Find rows that match multiple (two) criteria
I am trying to get a list of items that match on two criteria.
DATE PRODUCT CHANNEL
DEC 13 Cat Chow DRUG
NOV 13 Pupperoni DRUG
DEC 13 Meow Mix DRUG
DEC 13 Jerky ...
1
vote
2answers
72 views
Combine columns in Excel
Is there a way to combine 2 columns?
For example, I have a survey asking a Yes/No question.
But the survey puts all yes’ in one column, and no’s in another.
So it will look like
column 1 column 2
...
-1
votes
2answers
59 views
Fill a table from a RAND based formula in Excel 2010
I am trying to do a Monte Carlo simulation using Excel, but a lot of the tutorials I have found are either for older versions of the product, or are not quite what I am after. A simple example of the ...
0
votes
2answers
144 views
Comparing columns in Excel
I needed to take columns A, B, and C and compare D, E and F. Here's an example:
A B C D E F
Jump Smith ...
0
votes
3answers
402 views
How autofill all rows between two dates in excel with the second date as the incrementing limit?
That title might have made my issue sound more complex than it is...
I am trying to figure out take one cell.... say A1 and put a date in it.... then move down several thousand rows and give another ...
1
vote
2answers
177 views
Calculate geometric mean in Excel
I have some email network data in Excel as a edgelist meaning I have columns Vertex1, Vertex2, and then N columns of properties of that edge like how many emails were sent from one person to another. ...
-2
votes
1answer
158 views
Attendance marking in Excel
i've tried match/vlookup and other simple formulas which i can find over the net but to no avail. Appreciate if i can get any advice or help from here.
Problem:
I have an ordered attendance list, i ...
1
vote
1answer
57 views
Conditional drop down list
I have managed to do a conditional drop down list from another list – however I now can't seem to copy it down the 1000 rows I need it to work on.
For example, in column A the user would select ...
1
vote
2answers
225 views
Excel sum function that returns zero, if any value in the sum range contains zero
I would like Excel to return answer as zero if any of the values in that sum range is zero.
I have tried a combination of logical tests, but I can't get what I want.
0
votes
2answers
100 views
How to clear all value cells in an entire 2003 Excel workbook by one go?
How can I clear all value cells in an entire 2003 Excel workbook by one go?
I could do one sheet by going to special functions, however, if a file is composed of many sheets, it would take a lot ...
1
vote
2answers
45 views
Multiple queries in excel formula
I want to use two formulas in one line. The aim is to sum two columns, A and B, in C with two conditions:
First, if cells in A or B are blank, then formula in column C will return blank.
Second, if ...
0
votes
2answers
131 views
Excel IF statement, multiple answers
I'm looking to write an Excel IF statement that does the following
If cell A3 is over budget A1,
print text "Over Budget by $" and then Sum(a3-a1)
so, the result should look like:
Over budget ...
1
vote
3answers
52 views
Excel: link value once, then prevent change
For some budgeting spreadsheets I'm working on, I'd like to link each month to a value (in this case, a percentage). However, if the original percentage is changed I ONLY want to change values going ...
1
vote
1answer
90 views
Why are Excel formulas rendered as their plain text
I have a spread sheet sent to me by a friend and I want to add a column with some formulas in. Instead of containing the results of the formulas the new cells contain the text of the formula. Why? How ...
0
votes
2answers
432 views
Excel COUNTIF Array (Multiple Criteria)
I'm using this function:
=COUNTIF((B:B,"Superior")*(A:A,"Employee Effectiveness:"))= 2
The idea is to try and find how many times the value "Superior" is used adjacent to the cell with value ...
1
vote
2answers
94 views
Adding the next x number of cells in a row together
Each week, I will delete one of the columns (it's a set of forecasts held together by a week number at the head of the column; e.g., this week is week 46).
I want to add the next x number of cells ...
2
votes
3answers
264 views
Compare 2 sets of data in Excel and returning a value when multiple columns match
I have a data set for employees that contains name and 3 attributes (job function, job grade and location). I then have a data set for open positions that contains the requisition number and 3 ...
0
votes
1answer
49 views
Comparing, merging, calculating colums of data in Excel [closed]
I would like to create a formula that a) compares four columns of data (see below)
Sep Oct
name units name units
apple 2 apple 3
pear 3 pear 7
orange 4 ...
1
vote
2answers
176 views
Named Ranges, Cell References and Validation
If I have two cells and a named range "Mexico":
A1 contains the string: "Mexico"
A2 is the cell that has required data validation (drop down list).
I want A2's validation reference to be dynamic, in ...
0
votes
2answers
744 views
Copying Data from another Excel Workbook based on a matching id
I have 2 workbooks I am working with.
One workbook has an id and a category name.
The other workbook shows a name and category section that has an id number (but not the actual description).
...
4
votes
1answer
194 views
Function argument delimiter - Excel 2011 for Mac OS X
I have two Mac OS X machines, both running Microsoft EXCEL 2011.
One uses a comma to separate function parameters, e.g:
function(arg1, arg2)
Yet my work machine uses semi-colons, e.g:
...
2
votes
2answers
70 views
What is { } used for in Microsoft Excel 2010?
I came across this function in one of my worksheets and wanted to know what the curly braces are used for.
Here is an example.
{=array(sheet1!a1,sheet2!a3,sheet2!a5,sheet2!a6)}
0
votes
1answer
153 views
Rows disappear when removing duplicates libreoffice
I pasted a lot of data in Column A.
Then I removed duplicates:
Data > Filter > Standard Filter > More Options > No Duplication
Now I'm stuck in row 2632, i.e. I can´t scroll up to rows ...
1
vote
3answers
141 views
How to create a conditional format in excel 2007 from another cell
I have a sheet with 4 fields spanning A3:D3
in D3 I have a conditional format that turns the cell yellow if it equals the word "no contact"
How can I get Cells A3:C3 to also turn yellow if this is ...
0
votes
1answer
68 views
2D Lookup without unique column values
I have a 2 Sheets x and y as follows:
x
____________________________________________
State | 1 | 2 | 3
ME | | |
VA | | ...
0
votes
1answer
115 views
Excel convert MMM DD, YYYY to string
I am trying to convert a date string into a date format the strings look like this
Nov 28, 2005 12:00 AM
and i would like them in the format DD/MM/YYYY i have been trying to find a solution online ...
2
votes
1answer
2k views
How do I extract excel data from multiple worksheets and put into one sheet?
In a workbook I have 7 sheets(Totals and then Mon to Sat),I want to extract rows which have the word "CHEQ" in its cell (this is a dropdown list with two options-CHEQ/PAID)from all sheets. On my front ...
