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

learn more… | top users | synonyms (5)

0
votes
1answer
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
3answers
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
1answer
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
2answers
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
1answer
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
4answers
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
1answer
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
3answers
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
1answer
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
1answer
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
3answers
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
1answer
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
1answer
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
1answer
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
3answers
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
2answers
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
1answer
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
3answers
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
1answer
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
1answer
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
1answer
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
2answers
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
2answers
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
2answers
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
1answer
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
1answer
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
1answer
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
1answer
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
1answer
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
3answers
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
2answers
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
2answers
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
2answers
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
1answer
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
1answer
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
2answers
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
1answer
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
2answers
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
1answer
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
2answers
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
0answers
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
1answer
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
1answer
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
2answers
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
2answers
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
3answers
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
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
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
1answer
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
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 ...