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

**1**

vote

**2**answers

573 views

### Excel formula depending on whether a cell is text or not

=IF(F46=0,I45-C46,I45-F46)
This works. But now I want to include the following as well: Let's say F46 is text – I want the cell value to be I45 (the previous cell value) but if it's not text I want ...

**0**

votes

**3**answers

1k views

### How can I compare Excel serial dates WITHOUT converting to mm/dd/yy type dates?

I have a table that contains a number of values representing Excel serial dates. After a number of unsuccessful attempts to compare fields, my current approach is to do comparisons between serial ...

**0**

votes

**1**answer

572 views

### How do I fill in data using index, match, etc. in MS Excel?

I have data formatted like so.
1, 2, 3, 4, null, null, null
A, B, C, D, null, null, null
1, null, null, 4, 5, 6, 7
A, null, null, D, 8, 9, 10
I want to copy the data from row three into the ...

**2**

votes

**3**answers

3k views

### How do I `SUM` by multiple columns in Excel

I have a comma delimited file that includes two columns date/time (which imports as Excel's mm/dd/yyyy hh:mm custom format) and status of 1 or 0. The status represents a piece of equipment either ...

**-1**

votes

**1**answer

3k views

### Calculate age in days/months/years in OpenOffice

In need to find the age in days - months - years in OpenOffice.
There is DATEDIF() in Microsoft Excel. You can use it to find the difference in days/months/years between two dates.
Age Calculation
...

**2**

votes

**3**answers

286 views

### How to sum up numbers conditionally in Excel? [duplicate]

Possible Duplicate:
Sum Values in One Range Based on Criteria in Another
I have the following Excel document, which tracks hours needed to implement various tasks by the development ...

**0**

votes

**1**answer

252 views

### How to omit header values in Word formula?

I am new to superuser. Kindly pardon if I make some mistakes.
I am working with tables having numerical data in Word (no not even embedded Excel tables - don't ask why).
It so happens that many ...

**1**

vote

**2**answers

3k views

### excel formula to calculate average of consecutive 24 hour blocks?

Microsoft Excel 2010, input file of thousands of rows of hourly data, thus there are 24 values (rows) per day.
Let's assume that my data starts at B1, and let's assume that I put my average value for ...

**1**

vote

**1**answer

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

**1**

vote

**2**answers

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

**3**answers

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

**3**answers

576 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

**1**answer

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

**5**

votes

**2**answers

2k views

### Excel: Counting number of MAX values in a range

I am using Excel 2003 (version limited due to corporate policy) to keep a score sheet:
A B C D
1 Round | Rita | Sue | Bob
------+------+-----+-----
2 1 | 5 | 3 | 4
3 ...

**0**

votes

**3**answers

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

**1**answer

314 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

**2**answers

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

**0**

votes

**2**answers

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

**1**

vote

**3**answers

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

**0**

votes

**1**answer

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

**0**

votes

**1**answer

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

**2**answers

202 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

**1**answer

144 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

**1**answer

423 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

**1**answer

651 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

**1**answer

58 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

**1**answer

142 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

**1**answer

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

**0**

votes

**1**answer

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

**2**

votes

**1**answer

502 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

**2**answers

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

**2**

votes

**1**answer

223 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

**2**answers

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

**1**

vote

**3**answers

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

**2**answers

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

**2**

votes

**2**answers

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

votes

**2**answers

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

**0**

votes

**1**answer

3k views

### OpenOffice Calc - how to insert BLANK in a formula?

In Calc, as in most spreadsheet applications, an IF() function exists that can be used to control behaviour, for example inserting this formula in cell C1.
=IF(A1>B1;"A";"B")
will display A in ...

**1**

vote

**2**answers

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

**0**

votes

**2**answers

354 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

**2**

votes

**1**answer

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

**2**

votes

**3**answers

2k views

### Average of cells that match criteria and removing duplicates in Excel

I have an Excel spreadsheet where I want to remove the duplicate names and take an average for each person's score. I have a list of the following type:
Al 1
Bob 1
Bob 2
Bob 1
Joe 4
...

**2**

votes

**2**answers

2k views

### Get adjacent column value in OpenOffice.org Calc

I'm trying to get the value of adjacent columns in OpenOffice.org Calc.
I have several rows, each with a column that should have its value incremented by one when a column immediately to the right ...

**2**

votes

**2**answers

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

**1**

vote

**1**answer

2k views

### Prevent Excel 2010 user from pasting formatting into cell

Is there a way to lock a cell so that a user can copy/paste a value into it, but not copy formatting to it? Locking the worksheet doesn't prevent the user from changing the formatting of the cell by ...

**1**

vote

**1**answer

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

**0**

votes

**4**answers

2k views

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

**2**

votes

**2**answers

6k views

### What's the difference between “vlookup” and “lookup” in Excel?

What is the main basic difference between VLOOKUP and LOOKUP in Excel?
I read the main point is that you can specify a range of columns in VLOOKUP and then pick one to return the value. However, it ...

**3**

votes

**1**answer

1k views

### How to set 2 conditions / criterias for VLOOKUP / LOOKUP / etc in OpenOffice Calc (or Excel)

I have this spreadsheet that started as a silly aid for a game (Mafia Wars 2), but grew into a tricky spreadsheet question.
In the game your character have 9 "slots" for weapons and armors, 1 for ...

**0**

votes

**1**answer

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