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

learn more… | top users | synonyms (5)

1
vote
4answers
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
2answers
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
1answer
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
2answers
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
2answers
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
1answer
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
1answer
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
1answer
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
1answer
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
2answers
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
2answers
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
3answers
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
2answers
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
1answer
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
1answer
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
2answers
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
1answer
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
1answer
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
4answers
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
2answers
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
2answers
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
1answer
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
2answers
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
2answers
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
2answers
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
1answer
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
2answers
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
2answers
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
1answer
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
1answer
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
1answer
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
2answers
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
1answer
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
1answer
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
1answer
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
1answer
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
1answer
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
3answers
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
1answer
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
1answer
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
2answers
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
3answers
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
1answer
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
1answer
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
3answers
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
1answer
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
1answer
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
1answer
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
2answers
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
2answers
286 views

How to count occurences of “&nbsp;” in a cell

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