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

learn more… | top users | synonyms (5)

1
vote
1answer
327 views

Excel column comparing

I am working on comparing markup values between three different databases. I have each database in a seperate colomn. What is the formula to see if all 6 markup values are the same. To get a Yes if ...
0
votes
1answer
232 views

Delete rows in Excel based on returned value in row cell

I have a worksheet in Excel with values in some rows in column B that have been returned as zero (0) but still have a formula in the cell. I want to be able to delete just these rows using a macro.
1
vote
2answers
1k views

When drag-copying formula cells to the right, table-references are changed

I have a simple Excel table containing a formula that is referencing columns in another table, and is using the automatic table-range-names (not sure what they're correctly called) =A1 + ...
0
votes
2answers
157 views

How to find specific phrases in unspecified locations and add to new column on MS Excel

Sorry if the questions is not very explanatory. Basically I have a large sheet of data on prospect clients. The data includes their email addresses, however they're sparse and not in set locations ...
4
votes
3answers
19k views

Only show a formula result if it meets given criteria

I like to keep my Excel spreadsheets fairly clean. So, often times I'll end up writing statements like this: IF([formula x]=[value],"",[formula x]) What that essentially says is that if the ...
0
votes
3answers
10k views

Formula to return value from one column when condition is met for the first time in another

Example: Given a spreadsheet with four columns - A, B, C, and D, and row 1 is a header row... A2=1 B2=(Random positive integer) C2=(Another random positive integer) A3=A2+1 B3=B2-C2 A4=A3+1 ...
0
votes
3answers
625 views

Make numbered list with update feature?

Is it possible to do something like this in Excel? Like a list of active rows with an ordinal number in them. There is a column B. It needs to show an ordinal number which represents the active row ...
1
vote
1answer
136 views

How to reference cells from a previous worksheet?

I want to sum all the numbers from B75 to DY75 from worksheet Sheet1 and add that to the sum of all the numbers from B74 to DY74 in worksheet Sheet2 to get my total average. In other words: total ...
1
vote
1answer
485 views

Copying formulas in Excel

I have an Excel spreadsheet containing many sheets, most of which have formulas. I have managed to copy most of the sheets (with formulas) into a new Excel file. To copy the sheets I used Copy and ...
0
votes
3answers
1k views

Excel formula to bring back one of two values based on another cell being populated or Null

I am trying to create a formula that will reference a cell. If that cell is blank, I want it to bring back a value of "LTL Direct". If it is not blank (anything at all showing), I want it to bring ...
0
votes
1answer
452 views

Excel formula to change a number from + to – when word “Db” is next cell

I have a many long statements converted from text, the credits and debits all are in same column as plain positive amounts, to distinguish the credits to debits the cell next to the amounts shows the ...
0
votes
2answers
659 views

Spreadsheets: How do I perform functions on a set/array described by a row of (count, value) pairs?

I have excel 2000, google docs, and the latest open office calc available to me. I'm after a solution in any of those, ideally. I have a multiset of data described by rows of (count, value) pairs. ...
1
vote
1answer
340 views

Attempting to get data sorted and counted by Calendar Quarters in Excel

I am attempting to set up a formula in excel that will look at a range of data which has dates specified in c7:c30, and categories Active-"A" and Completed-"C" in L7:L30. I am attempting to have ...
1
vote
3answers
1k views

How to easily change columns into rows in Excel?

I have a huge Excel table that I need to transform into paragraphs for a Word report, and I can't find an efficient way to do it The source looks like this: And I would ultimately need something ...
1
vote
1answer
275 views

Alternative, more efficient scraping method for a noncoder, than Google doc's importxml and xpath?

I've searched throughout the net for a simple solution, but it seems everyone has their own unique method (coding language) of achieving this. I'm only just beginning to learn Linux, and my coding ...
0
votes
1answer
311 views

Excel 2007: Need a count when comparing differences in two columns

I have one column that contains budgets and another that contains actuals. How can I get a count in a cell above the actuals column that will tell me how many of them are under their budgets? ...
1
vote
3answers
6k views

Find a non-case-sensitive text string within a range of cells

I've got a bit of a problem to solve in Excel, and I'm not quite sure how to go about doing it. I've done a few searches online, and haven't really found any formulas that seem to be useful. Here's ...
0
votes
1answer
2k views

Excel INDIRECT function and conditional formatting - highlighting a row

I'm having an issue with conditional formatting using the INDIRECT function. I'm doing something similar to Using INDIRECT and AND/IF for conditional formatting , but the only answer there isn't ...
1
vote
2answers
204 views

Set a formula on many cells with minimal manual edits

I need to set the following formula on many cells: =VLOOKUP(MAX(Historical!$A$5:$FZ$99999),Historical!$A$5:$FZ$99999,7,1) This formula looks up the most recent date in the History table, and ...
0
votes
1answer
669 views

Searchable tags in Excel?

I'm building a database of spiders, organizing them by name, country, and region within that country. The problem is that many of the spiders exist in multiple countries and regions within that ...
0
votes
1answer
155 views

Google Docs / Exel - need to know how to add formula

I'm using Google Docs / Exel and can't work out how or where to add a 'formula'. I have a column "i" which is the number of items. I have another column "j" which is the cost of those items. Column ...
0
votes
2answers
2k views

Lookup Multiple Results for Multiple Criteria

I've got a list of parent SKUs for items I need to create in my inventory system. This list has been finely paired down to the 165 products we would like to carry. However, each one of these 165 SKUs ...
0
votes
1answer
59 views

Auto updating similar formulas

I'm trying to see if there's a way to get my Excel sheet to auto update with new formulas as I enter it. For example if D4 down to D27 was the same formula(=SUM(B4/25)) and I wanted to change it too ...
2
votes
1answer
164 views

Why is Excel showing 'c' as the arg separator for fomulas?

In Excel 2007, I have a cell with "=if(true,1,0)" as the contents. If I hit the ENTER key I get an error dialog which says, "The formula you typed contains an error." The flyover help for ...
0
votes
1answer
58 views

Excel 2007 How To Reference A Seperate Cell In WS1 From A Common Cell In WS2

I have a simple file with two worksheets in. In the first worksheet (a product list) are a number of columns, including Product Code and Product Description. In the second worksheet (dispatches in ...
2
votes
2answers
1k views

Turn Excel spreadsheet into a formula

I have an Excel spreadsheet that has a complex computation that is not trivial to turn into a macro or a single-cell formula. The spreadsheet has about 10 different inputs (values a human enters in ...
2
votes
1answer
567 views

Split time in arbitrary periods, EXCEL

I have a list with date and quantity of items used IE: 2009.03.18 -1 2009.06.05 -2 2009.06.22 -1 2009.06.29 -2 2009.07.14 -1 2009.07.14 -1 2009.07.14 -2 2009.07.20 -2 2009.07.30 -1 ...
0
votes
1answer
1k views

How do I create a dynamic formula on Excel?

On Excel, I have a DDE formula on B1 which reads =server|info!someText.data I want to change the formula so that someText is written on A1 and then reference it on the DDE formula. Something like ...
0
votes
2answers
947 views

Excel: How to print unique pages with only one sheet

I am trying to create a template in MS Excel to print similar pages but with an unique ID within each printed pages (e.g. ID: 001, 002 and so forth) but printing from one sheet only. I am creating ...
2
votes
1answer
257 views

Excel: Weighted SUM of IFs [duplicate]

Possible Duplicate: Sum Values in One Range Based on Criteria in Another I have a spreadsheet with a list of weights and another list based on a drop-down selection with the choices "Yes" ...
3
votes
2answers
1k views

Excel Conditional Formatting

Highlight all non-blank entries in a column, except for the column header, that do no fall in some range. Here's my attempt: ...
3
votes
3answers
14k views

Microsoft-excel newlines and tab

Current I use "CONCATENATE" to join string in Excel, but I want insert character newlines and tab in string joined by "CONCATENATE". Example: I've string below when I completed join string. "Today ...
0
votes
2answers
4k views

Cell to Equal Value of Different Cell based on Match in Array

Need to Column C to equal the text in Column G based on the match the value in Column B. I do have Column C matching the row in which the value is found (based on columns B and F) Column C's Formula ...
0
votes
1answer
468 views

Excel 2010: Giving another cell value to an “x” (check) in a cell

Hopefully I'll be able to explain clearly what I would like: I have a spreadsheet about a car pool. I have to put an "x" (check) in a cell when there are seats available to transport people, but I ...
0
votes
3answers
5k views

Sum only if any one of six cells have a value

I would like a cell (total) to sum 6 other cells only if one of those 6 cells have a value. If none of the six have a value i do not want anything to appear.
0
votes
2answers
184 views

Finding duplicates in a column where the cells hold a formula

I have a workbook with 3 sheets. Sheet "PS", column A contains a list of player names that stays static. Each week, each person in column A sends me a name from an available list. They are allowed ...
3
votes
2answers
3k views

In Excel, can I provide a “default value” in case my formula returns #NA?

For example, Say the value of a cell is: IF(ISNA(VLOOKUP(A3,somesheet!G:J,3,FALSE) * VLOOKUP(A3,someothersheet!A:D,4,FALSE)), 0, VLOOKUP(A3,somesheet!G:J,3,FALSE) * ...
1
vote
1answer
2k views

Combine cells with binary values in Excel

Imagine I have a couple of eight cells which all contain a binary value; so it shall be something like Cell A1 has value 0; Cell A2 has value 1; Cell A3 has value 0; etc. What I would like to have is ...
1
vote
3answers
4k views

How do I transpose data in columns to rows?

How do I transpose data in columns to rows? If the data is: Joe some city some state Bob no city no state and I use Paste Special » Transpose, I get Joe some city some state Bob ...
-1
votes
2answers
811 views

Microsoft Excel - Generating random time invtervals

I have a list of employees around 30. I need to create a simple table that will assign them a specific time that they are to be on the phone and 24 hours later excel will automatically(randomly) ...
1
vote
2answers
1k views

Count negative values within date range (month)

I have values in a table like this Date Values 01/01 5:35 02/01 -7:08 02/01 3:45 15/01 -4:05 02/02 3:25 13/02 4:25 19/02 6:20 I would like to count by month how many 'Values' ...
1
vote
2answers
8k views

Excel get cell value which is NOT Match in a row

I would like to search C2:I2 which is the cell value NOT equal to "=No=", which is "ABC", and the formula I would like to store in A2. I was able to look for some formula which works with MATCH(), ...
2
votes
1answer
647 views

Excel formula - getting mapping details from 2 sheets of the same work book

Please can you help me with the below excel formula? Sheet1: Col A = Business use case number (BUC001, BUC002...) Col B = Business use case definition corresponding to the Business use case number ...
0
votes
2answers
476 views

How to round up number in Excel

Let's say we have an array of nubmers $9,333.00 $5,733.00 $21,333.00 $21,833.00 How can we make them round up to $9,500.00 $6,000.00 $25,500.00 $22,000.00
6
votes
4answers
47k views

How to combine values from multiple rows into a single row in Excel?

I have a data dump in Excel that consists of annual customer data for two different values. The data was provided with a separate row for value for every year and customer. I.e. it looks like this: ...
2
votes
2answers
3k views

Excel VBA Function for Value AND Format Lookup

I need a custom function to lookup both a cell value and copy the source format. I have a list of values in two columns. The first is of integers and the second column is the corresponding text ...
0
votes
4answers
3k views

Smooth a curve by calculating / interpolating source missing values

I have a table of values like this one : 18 18 18 46 46 46 46 46 57 57 57 57 57 57 69 69 69 69 69 69 64 64 64 64 64 64 56 56 56 48 48 48 44 or if you prefer : 18 0 0 46 0 0 0 0 57 0 0 0 0 0 69 0 0 ...
0
votes
1answer
277 views

Lookup is failing to accurately use the lookup value

I am using two Excel workbooks; one is the backend for the other. In Book 1, there are sheets sheets with a $Timeline, $ClientInfo, and $VendorInfo. Book 2 is meant to be a summary record of different ...
1
vote
1answer
2k views

MS Excel 2010 - Unprotect Grouping Controls

I have an Microsoft Excel 2010 spreadsheet that I want to share with my team on a read-only basis; I want to prevent them from modifying it, so that I keep control of changes. It's quite large, and ...
1
vote
3answers
4k views

Microsoft Excel - Count the number of values each name has in a single column

I have a CSV file containing 100,000 rows. Each row consists of information under a number of columns, such as "Date", "Page Accessed", "Page Type", "IP", "User Surname", "User Forename", "User ...