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

**1**

vote

**4**answers

12k views

### Calculating the calendar week in Excel

I am using the following formula to calculate the calendar week if referenced to a cell with date:
=INT((A1-DATE(YEAR(A1);1;1)-WEEKDAY(A1))/7)+2
but it does not work properly. If I enter 24.08.2012 ...

**1**

vote

**2**answers

358 views

### All cells in column as graph source except header

I want to create a graph of all values in a column, except the first which is a header. I could do this by selecting the following as Series X Values:
=Foo!$A$2:$A$8
Problem is that I'd have to ...

**1**

vote

**1**answer

88 views

### Excel lookup not working as I expected

I have this:
d a 1 =VLOOKUP(A1,$B$1:$C$5,1)
e b 2 =VLOOKUP(A2,$B$1:$C$5,1)
f c 3 =VLOOKUP(A3,$B$1:$C$5,1)
g d 4 =VLOOKUP(A4,$B$1:$C$5,1)
h e 5 =VLOOKUP(A5,$B$1:$C$5,1)
...

**1**

vote

**2**answers

111 views

### How to name column headers (column B to X) based on values in column A?

Consider the following excel worksheet:
| ... | H | I | J
--+-----+--------+------+-----
1 | ... | obsDay | obs3 | obs4 ...etc
--+-----+--------+------+-----
2 | ... | 3 | |
...

**0**

votes

**2**answers

668 views

### Excel 2010 formula conversion to Excel 2003

I am by no means overly proficient with Excel, I know enough to do the basics.
After much forum and soul searching I had come up with the following formula, which I will discuss what it is supposed ...

**3**

votes

**1**answer

698 views

### Strange Excel IF formula evaluation

In a particular strange workbook,
In cell A1, I key the value 0
In cell A2, I key the formula =IF(A1="APPLE", 1, 0)
The result in cell A2 gives 1.
I tried to replicate the same in a new workbook, ...

**1**

vote

**1**answer

2k views

### Finding non blank cell then in same row if another cell is blank then return value of another cell

I have three columns. "Site ID" with a value, "Ready" with date and "Installation Completed" with date.
Now I want that I search whole column B for non-blank cell, if I found then it checks column C ...

**1**

vote

**1**answer

233 views

### Search for data in Excel 2010 that can be anywhere in a column

I am attempting to search for data anywhere in a column. I have the following formula but I think I need a wild card. My data may be in cell J2012 instead of J2. Suggestions?
=IF( ...

**1**

vote

**1**answer

1k views

### Excel array formula for max with multiple/ variable criteria

G'day there,
I have a sheet that contains 365 rows, representing dates from a referred Start Date (for the following year). There are about 30 columns each of which has an agency name.
The cells in ...

**1**

vote

**2**answers

474 views

### How do I highlight rows based on latest time stamp in Excel?

I have 4 columns, A B C D.
Column A has a designed value.
Column B has a reviewed value.
Column C is empty.
Column D has a formula which returns false if value in column A does not match value in ...

**1**

vote

**2**answers

917 views

### How do I fill weekdays on a calculated cell?

I'm trying to autofill cells to exclude weekends. The thing is, the date is calculated and the context menu for fill weekdays is grayed out because of it.
So basically I'm trying to say something ...

**1**

vote

**3**answers

1k views

### Create a hyperlink from URL and title in Excel

I'm trying to construct a hyperlink from a URL and a name in different cells.
a1 = http://www.example.com/
b1 = example
c1 = <a href="http://www.example.com/">example</a>
What I want ...

**0**

votes

**2**answers

191 views

### How do I add a value to a cell in Excel if it's an int?

I have an Excel table with some cells that are numbers and some cells that are strings. I want a function that can add search through the table, and if the cell is a number, add it(+) and if it's a ...

**1**

vote

**1**answer

125 views

### Calculating number of occurrences of a character

I export data from an application to Excel sheets.
The first column of the exported data (Column A) has some information, but contains text like 16.1 or 16.1.1, etc. I need to create a column right ...

**3**

votes

**1**answer

460 views

### ADDRESS function nested in SUM returns Err: 502

When working on one problem, I came upon something that confuses me and was hoping for some clarification.
I am trying to use the SUM() function in a very round about way.
The formula
...

**0**

votes

**2**answers

4k views

### Insert cells in Excel without updating adjoining formulas

Sometimes I visually compare two datasets by pasting them side-by-side in Excel with a formula in the middle like =A1=C1. This will return true or false based on whether the cells are an exact match. ...

**2**

votes

**1**answer

17k views

### Excel, how to find column number of min value?

I have a table in Excel.
Can anyone help me find the column number of the cell where the min value of the row is?
Example: 200 is the min value in row1 and has the column number 1.
Is there a ...

**0**

votes

**1**answer

97 views

### Mathcing data that includes “string1” and ends with “string2”

In Excel or Access(preferably) I want to do the following:
I have company names matched with their zip codes like:
Ozer LTD-55200
Gelibolu INC.-34759
and also I have some strings like ze and ...

**2**

votes

**4**answers

3k views

### How to calculate how much I've worked per day?

I'm having a problem when I try to calculate how much time I've worked per day by using an Excel formula. It returns valid results for when I've worked until 12:00 PM but if I passes it, values come ...

**-5**

votes

**2**answers

74 views

### Add a number of cells that only have a 1 in them [closed]

Is there any way for me to add a bunch of cells that only have a 1 in them? If they have a 0 in them, then this should not be added to the total. This should also work if the list is filtered too.

**0**

votes

**2**answers

4k views

### Using an insert if formula in excel with 3 conditions

Trying to do an insert if formula in excel like the following:
In my column K I want three conditions:
Date is Today or older (ie project is due today or earlier) = RED
Date is Today + up to 7 days ...

**2**

votes

**1**answer

635 views

### How to have a cell calculate only once

I have cell N29 calculating some values based on streaming data. Once a condition is met in cell Q29, the calculation or final value should be passed to cell O29 and no longer calculate in either ...

**4**

votes

**2**answers

739 views

### Counting “0” Stock Values Generated by Pivot Table

I have an inventory ledger. It looks like this:
Tran Type Txn Date Item Quantity
Opening Balance 6/30/12 Item1 4
Opening Balance 6/30/12 Item2 7
Shipping ...

**0**

votes

**2**answers

169 views

### Compute the first digit of an integer in Excel

I have a spreadsheet with column B having integers. I want to get the first digit of each entry in column B in the corresponding row of column C.

**15**

votes

**2**answers

84k views

### What is the Excel hotkey to re-calculate all formula in sheet?

I have a sheet that contains many formulas. But they do not recalculate automatically when I change input.
I'm looking for a hotkey, that can re-calculate the sheet. According to this page, F9 ...

**5**

votes

**1**answer

1k views

### Conditional formatting to highlight the whole row

I would like to highlight the whole row 5 if J5="T".
I used to do it with the old Excel, the 2010 conditional formatting ignores it. So my rule is like this and it does not work. I used the formula ...

**0**

votes

**2**answers

6k views

### How do I replace all employee numbers with their corresponding name in Excel?

I have a workbook with a sheet that is formatted like this:
| 1234 | Name 1 |
| 2345 | Name 2 |
| 3456 | Name 3 |
There's then another sheet with data like this:
| Hours | Employee ID |
| 18 | ...

**3**

votes

**2**answers

1k views

### How do I create a data validation list WITHOUT using cells?

I have a simple data validation dropdown that I'm trying to do, but I DO NOT WANT to use cells to populate my list.
Something similar to what's below, but this obviously isn't working.
What ...

**2**

votes

**1**answer

148 views

### Concatenate cells in Excel 2010

I am trying to Concatenate three cells. I want the end result to read in all numbers as the two following ways.
#####-000-####
#####-###-000
When I try to concatenate I am losing 2 zeroes. Anyone ...

**3**

votes

**1**answer

7k views

### How to reference variable sheet in OpenOffice Calc?

I want to reference a cell in another sheet, and the formula I get when pressing = followed by changing sheet, clicking on the cell and pressing Enter, gives me
'Sheet2'.A2
Which is fine, except ...

**0**

votes

**1**answer

3k views

### Selecting the date of every Monday and Wednesday from a date range

I need a formula or a macro to help automate a grade sheet's dates.
We have class every Monday or Wednesday only. I would like to vlookup from an input table of each quarter's date range, for ...

**2**

votes

**2**answers

15k views

### How can I check to see if two Excel spreadsheet columns have the same exact data?

I have 2 Excel spreadsheets that I must do a side by side comparison on. I am comparing one employee spreadsheet to another spreadsheet of the same employee and there are columns that need a 'Y' or ...

**0**

votes

**1**answer

2k views

### How flexible is the 'indirect' function?

My curiosity pushes me to ask this question.
If I were to have a series of functions that referenced a different column in a worksheet but all ended on the same row of data is there a way to point ...

**2**

votes

**1**answer

736 views

### Excel 2010 - more than 1 calculation within an IF() statement

I have a situation where I need to calculate shipping values based on the length of the supply chain. Easy, however I need to have instances where an increased amount is required based on specific ...

**0**

votes

**1**answer

2k views

### Converting number string to a time format: hh:mm [duplicate]

Possible Duplicate:
Convert Excel Text string to Time
I must enter appointment times in a workbook.
For instance I have cell E2 to input the time. I'll input, lets say 1500. How can I ...

**1**

vote

**1**answer

939 views

### Is there a way to sort digits within a cell?

I'd like to know if there's an Excel Function, or a formula, that can be used to sort digits within a cell.
For example, if the value of A1 is 6193254807 and B1 has our formula with parameters set to ...

**1**

vote

**1**answer

112 views

### Excel 2007 - Adding line breaks in a cell and no line over 50 characters

I have notes stored in an Excel cell. I add line breaks and dates every time I add a new note.
I need to copy this to another program, but it has a line limit of 50 characters. I want a line break ...

**1**

vote

**3**answers

3k views

### Convert Word 2007 equations to Microsoft Equation

I am about to submit my article to one of the scientific journals.
However, it is very likely that they would only accept files in .doc, whilst my article is written in .docx and tons of formulas ...

**0**

votes

**1**answer

3k views

### Auto create folders using Excel field entries

I need to generate folders containing certain Excel field entries:
Additionally, I need to append a date to the folder name based on that entry
So If I have the above Excel table, I'd need the ...

**0**

votes

**1**answer

163 views

### excel 2007 enter new date

I’m try to create a excel template which will have three fields I like to update automatically when it’s open.
Fields which this template will have
Current Date: 02/07/2012
Sheet Number: 25
...

**0**

votes

**2**answers

1k views

### Autofill date pattern with Excel

I want to use Excel to fill a range of dates, the first and 15th of each month to be precise.
My spreadsheet currently looks like so:
When I drag down, it looks like so:
Instead of continuing ...

**2**

votes

**3**answers

4k views

### List of ALL Excel Functions by Version

Is there a table somewhere showing all the Excel functions and the versions in which each is supported? I'm trying to make sure I can share my 2010 worksheets with 2003 users, and don't want to use ...

**1**

vote

**1**answer

910 views

### Refering to a Cell in a Different Workbook

I work between different spreadsheets, and I like to have one main workbook that links to certain cells in other workbooks.
For example, I will do a statement of business activities workbook that ...

**1**

vote

**1**answer

3k views

### How can I calculate the subtotal for filtered cells that meet certain criteria?

I'm trying to create a subtotal for a range that excludes rows that don't have a wholesale price. I have a range of products, prices and units that have subtotals by brand, although the brand subtotal ...

**2**

votes

**3**answers

8k views

### How do I find out what's linked to my Excel Worksheet?

I've got an Excel 2007 worksheet that I inherited, and each time it opens, I get the dialog box asking if I would like to update values from an outside, linked worksheet. I'm trying to track down if I ...

**4**

votes

**1**answer

439 views

### In which order does Excel process its formulae?

I've got a fairly large spreadsheet with major calculations going on, and it's starting to slow down every time a value that's part of a calculated field is modified. I'm in the process of optimizing ...

**0**

votes

**1**answer

112 views

### Do I need a helper column, or can I do this with a formula?

I am using this formula
=IF((LEFT($B26,2)="<p"),0,IF($B26="",0,IF($F26<>"",0,(FIND("""../",$B26)))))
To parse data similar to the following.
...

**0**

votes

**1**answer

13k views

### Apply VLOOKUP formula to multiple sheets

How can I apply a VLOOKUP formula to multiple sheets in Excel 2010?
Our formula is written and it is working on our Sheet 1, but we need to search 3 Sheets (within the same Workbook), preferably ...

**-2**

votes

**2**answers

71 views

### Putting the few rows into one Excel 2007 column instead

Is there any way of putting the few rows I have in an Excel 2007 spreadsheet into one column instead?
a b c
d e f
g h i
to
a
b
c
d
e
f
g
h
i
This is just a sample try out. I am ...

**1**

vote

**2**answers

286 views

### How to count occurences of “ ” in a cell

I was given a spreadsheet containing a bunch of HTML lines sampled below.
<nobr> <a href="../Descriptive_Addresses.htm"><b>address: ...