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

**0**

votes

**1**answer

363 views

### Transpose table to a single-column list

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

**3**answers

6k views

### Excel: Using ADDRESS to create Array Formula

I'm trying to replace the hardcoded references E13:E15 with relative references:
=+SUM(LN(INDIRECT("E13:E15")))
I've tried variations of below, in cell e16, but nothing seems to work:
...

**0**

votes

**1**answer

14k 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

**2**answers

2k 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

**1**answer

998 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

**4**answers

610 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 ...

**1**

vote

**1**answer

904 views

### How to replace cells containing individual letters with another value?

In Excel, how do I write a formula that looks at a range of cells (say, A3 to A16), checks whether any of them are each individually letters, and if they are, replaces those values with "0"?
Replace ...

**3**

votes

**3**answers

1k 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

**1**answer

828 views

### Libreoffice Calc - Fill with Sum is not working

I want to add 2 columns of data and output the sum into a third column, with a =sum(a1+b1). With many rows of data, I try dragging down the highlighted cell to Fill the rest of the output column with ...

**0**

votes

**1**answer

236 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

**3**answers

131 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 ...

**4**

votes

**1**answer

4k views

### using an and() formula with an if statement in an array formula in excel

OK so I have a list of client returns in a spreadsheet. Each client also has a risk category assigned to the (risky or safe) and the partner assigned to them (P1 or P2).
Here is the an example of ...

**0**

votes

**1**answer

245 views

### Wildcards in logical statement in excel?

I have the following formula:
{=SUM(('Data 2'!D:D>=VALUE("17-September-2012"))('Data 2'!K:K="0 out of 1"))-SUM(('Data 2'!D:D>="None Specified")('Data 2'!K:K="0 out of 1"))}
This works fine to count ...

**1**

vote

**1**answer

153 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

**3**answers

204 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

**2**answers

291 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

**1**answer

172 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

**3**answers

279 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

**1**answer

1k 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 ...

**0**

votes

**1**answer

731 views

### How to convert currencies in Excel based on historical conversion rates?

I want to make a small program in Excel that allows the user to convert certain currencies (USD, Euro, AED, CHF and GBP) to Kuwaiti dinar. The user can set the amount, the currency and the month, and ...

**3**

votes

**1**answer

953 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

**2**answers

454 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 ...

**2**

votes

**2**answers

13k views

### Calculating a duration in excel from date and time

I have 2 columns in excel, each column includes a date and a time in the following format: 'dd/mm/yyyy hh:mm'. One is a start date/time, the other is an end date/time. The end date/time could be ...

**0**

votes

**2**answers

6k 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

**1**answer

8k 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

**1**answer

260 views

### Mapping of child elements

I can't find one good way to solve this problem. I will try to sum the issue as best as I can:
I use Hyperion Essbase Excel ad-in to navigate inside OLAP cubes. What I am trying to accomplish here is ...

**0**

votes

**1**answer

235 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

**1**answer

2k 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

**1**answer

712 views

### Excel Formula For Grading

I am looking to create a formula in Excel to sum a number of grades on student assignments. Here is the relevant info:
There were 11 assignments total, with grades entered into 11 columns (Columns ...

**2**

votes

**3**answers

591 views

### Auto Updating an Excel Filter

I'm using excel 2010.
I have had a look at other "auto-update" and from what I saw they wanted you to type some information in to make it "refresh" the auto-update.
I have is two separate spread ...

**1**

vote

**2**answers

4k 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 _
...

**3**

votes

**2**answers

288 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:
...

**2**

votes

**2**answers

111 views

### I want to count the number of cells included in a formula

I've been looking for this answer and I'm sure it's embarrassingly simple but I can't find it anywhere.
I have a very simple but large formula =SUM(H2:H5,H7,H22:H23,H28:H30,H33,H41,H43:H46.....etc) ...

**1**

vote

**1**answer

4k 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

**1**answer

159 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 ...

**2**

votes

**2**answers

1k 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

**1**answer

513 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

**2**answers

149 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

**1**answer

85 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

**2**answers

773 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 ...

**1**

vote

**0**answers

25 views

### Excel: Check if column A has any values from Column B, and delete that value from Column A [duplicate]

Possible Duplicate:
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 | ...

**3**

votes

**1**answer

6k 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 ...

**4**

votes

**1**answer

495 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 ...

**1**

vote

**2**answers

3k views

### Math formula editor with export to SVG, EPS or AI

I'm looking for a free formula editor, which can export formulas to Vector-based formats. (svg, eps, ai, pdf, cdr, etc..)
Do you know something?

**0**

votes

**2**answers

174 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 ...

**1**

vote

**3**answers

330 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

**1**answer

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

**1**answer

78 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

**1**answer

326 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

**3**answers

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 ...