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

learn more… | top users | synonyms (5)

1
vote
3answers
326 views

Is there a tool for windows that creates formula-pictures out of a text-formula

I am looking for a tool that would batch process text-files containing formulas (possibly latex style) and convert them into a gif/png/jpg. Since I want to do a batch conversion, the tool ideally runs ...
0
votes
1answer
1k 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
77 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
313 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
2k 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
236 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: ...
1
vote
1answer
69 views

import formula from corrupt / large excel workbook

I am trying to import formula from a closed Excel workbook that has gotten overly large and hence wont open without crashing Excel. I have a copy of the data but want to get at the formula in one of ...
0
votes
2answers
998 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
99 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
353 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
100 views

How to Add one column based on the values of another column AND display into another sheet

I need to add all column G values together for each Value in column C (ndc) and display them into another sheet listing just NDC and then Total next to it. How would I go about doing that? Thanks
0
votes
1answer
213 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
353 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
124 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
95 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
447 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
2answers
165 views

How can I check cells for number series?

I have a bit of a problem evaluating an excel cell. Example: M M M M M M M M M 1 2 3 4 5 6 7 8 9 2;5;7 1;9 3;5;7;9 I have a number of excel ...
1
vote
2answers
4k views

How to sum if there are values in range of cells and otherwise leave blank

How do I sum a series of cells only if there is a number in any of the cells (otherwise leave it blank)? The current formula I am using keeps returning a zero when there is no number in any of the ...
0
votes
2answers
1k views

Positive result, negative result and current balance. How do you make starting balance show current result?

I have 3 columns: Column A shows positive result and if the result is negative then it is in a column B. Column B shows negative result and if the result is positive then it is in a column A. ...
0
votes
3answers
3k 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
4answers
8k views

Excel: Change all cells with one character to something else

Is there a formula I can use that will change all cells with one character to something else? For example, I have cells with single letters and no matter what the letter is I want that cell to ...
1
vote
2answers
354 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. ...
-1
votes
1answer
998 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
119 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
807 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.
1
vote
1answer
93 views

excel / open office - append an incrementing value to all non-unique fields

I have a large table of about 7500 store names. I need to search through those names and, if they are not unique, append an incrementing value, for example: store_1 store_2 etc. Anyone know how to ...
2
votes
1answer
94 views

MS Excel - splitting a formula into individual cells?

I'm not sure if this is possible, or if I'll have to do it manually, but I have lots of cells in the following format: =87.12+56.52-16.50+98.21-9.51 If possible, I'd like to break it up into ...
0
votes
1answer
76 views

Excel Formula For Re-arranging Numbers

I have got a bit stuck with trying to make a formula in Excel, and last time I asked a question here I got great help, I have come back again. At the moment I have column A with sets of product id's. ...
2
votes
1answer
3k views

How to count how many rows contain particular values in a particular set of cells in libreoffice and excel

This Calc file : http://dropcanvas.com/521xc/107 have a sheet called "Etat" and a sheet called "Total". In Etat.F20 I have a formula that counts how many rows in Total have the value "Bug" in the D ...
0
votes
2answers
446 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
73 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
390 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
80 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
412 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
5k 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
926 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
879 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 ...
1
vote
2answers
341 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
1k 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). ...
1
vote
1answer
82 views

Microsoft Excel Price Formula

I am doing a formula that takes a cost x a mark up % to get a sell price. $11.47 x 1.35 = $15.48. I then need to go and copy and paste values to remove the mark up for my customers. When I do ...
0
votes
1answer
60 views

How to edit a formula that I already have to take into account another cell's data

I have an Excel sheet that keeps track of bonus for our paralegals. Because we need the clients to be listed in one sheet all the paralegals are also in one sheet instead of each having their own. I ...
0
votes
1answer
1k views

Excel 2007 conditional formatting rule using formula to highlight a row based on matching text between columns

I am checking accountability of whether my office is creating source acknowledgement reports "C", recommended whenever a reference document "A" is read, and required whenever an article "B" is ...
2
votes
1answer
722 views

Excel2007 Pivot Table: Get Percentage with respect to number of different values in a field

I have a pivot table based on the answers of N persons to M different questions. Simple Example (in reality their might be more categories for answering than "yes" and "no" and there might also be ...
4
votes
1answer
858 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
84 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
434 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
220 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
119 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
2k 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
7k 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 ...