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

**3**

votes

**1**answer

854 views

### Absolute reference all cell references in a Microsoft Excel formula?

For a Microsoft Formula like this:
=IF(Data!H5=3,0,IF(Data!H5<=Data!H19,3,IF(Data!H5<=Data!H20,2,IF(Data!H5<=Data!H21,1,0))))
Is it possible to mass convert all the relative cell ...

**0**

votes

**2**answers

3k views

### How to make a relative formula in a Microsoft Word table?

Example
2 2 =A1*B1
produces correct result, but when copied to a new row
the formula make static reference to Row1.
I tried this and it works
2 2 =PRODUCT(LEFT)
BUT must be able to ...

**2**

votes

**1**answer

747 views

### How to change a function/formula in a whole column where I also have empty cells

I have a column with a formula/function that has other cells as parameters. However in the column, every few rows I have a subtotal and blanks, for readability.
Now I decided to change the ...

**1**

vote

**2**answers

375 views

### Get date in Excel and print as plain text in cell

Is there a way in Excel to get today's date, then convert it into plain text so it can't be changed again when you open the file at a later date?

**2**

votes

**5**answers

1k views

### Excel not properly recalculating values

I have an Excel sheet with values in it (this sheet is generated by a custom perl script, but I don't think that's where the problem lies). In it, I have a formula:
...

**2**

votes

**2**answers

25k views

### Displaying a value only if another cell has a value in Excel

I'd like to display an empty box (HTML entity □ □ or any other similar box will do) in Excel in a cell if and only if another cell has a value.
I've tried =IF(ISBLANK(C8),,□), but that ...

**4**

votes

**4**answers

835 views

### Spreadsheet forumula questions to pick a random DVD

I have a list of my (279) DVD’s in an OpenOffice Spreadsheet (version 3.3.0) on my Windows 7 laptop and I want to create a formula to randomly pick a film to watch (for occasions when I can’t make up ...

**2**

votes

**4**answers

3k views

### Excel formula to get the penultimate value in a row

Imagine I have a row of data in excel, like this:
[A] [B] [C] [D] [E] [F] [G] [H]
[1] Data 5 8 12 32 15 9 89
I want a formula that will return the ...

**2**

votes

**1**answer

2k views

### In Excel, doing a “Freeze Panes” on the right-most column instead of the left-most?

My understanding of Excel's "Freeze Panes" feature is that you can only freeze the left-most column(s), or top-most row(s).
However, I have a table where a very important column needs to be located ...

**1**

vote

**4**answers

326 views

### Determining whether VBA is necessary in Excel, or if a pivot chart/table can suffice

I wish that I had a more precisely descriptive title for this question, but I am not very knowledgeable with Excel spreadsheets or VBA (I'm primarily a Java developer, and don't play much in the ...

**1**

vote

**1**answer

8k views

### Excel: Calculating working hours between two dates

Summary:
I know that the function NETWORKDAYS can be used to find the number of business days (=excluding weekends and holidays) between two dates. But what if I want to know the number of business ...

**2**

votes

**3**answers

228 views

### Excel formula: can MATCH recognise 'n'&“01”, or 'n'&“02 ”

I have an Excel sheet (source) that has simple ID numbers in column A (01 to 40000). In another sheet (child) I have these same ID numbers in column A but with either an additional 01 or 02 added on; ...

**1**

vote

**1**answer

5k views

### Excel Help: Fill Tool - Drag to the side (across columns) but increase the formula by Row Number

There are answers out there to this question, but all of them have been under explianed so hence to difficult to coprehend and use them to my advantage.
I want to do the seemingly simple (but not) ...

**11**

votes

**1**answer

28k views

### How do I put logical operators in an Excel =IF Formula?

I'm trying to enter a formula to display text according to an IF condition. The best I can manage is something like...
=IF(myval>=minval & myval <= maxval, "OK", "Not OK")
But this ...

**1**

vote

**1**answer

87 views

### Using Conditional Formatting with Formulas

I have a spreadsheet with approximately 2000 rows. I want to color code the rows based on the following conditions:
If T2 is less than U2, color the row
red; if T2 is greater than U2, color
...

**2**

votes

**1**answer

179 views

### Excel Help: Data Input Help

Everyday I download data from a site that will have rows each filled with individual data for clients. I'm able to input the data into excel as a whole but after that I'm having trouble figuring out ...

**3**

votes

**2**answers

968 views

### How do I stop Excel from adding double quotes to my formula?

this works:
{=MEDIAN((Table1[MonthFinish]=201012)*(Table1[Days]))}
but if I put 201012 into cell A3, this doesn't done work:
{=MEDIAN((Table1[MonthFinish]=A3)*(Table1[Days]))}
when i do Evaluate ...

**1**

vote

**2**answers

1k views

### Merge two Excel sheets

I have two 'tabs' in my Excel file and I would like to merge the two tabs into one sheet (in the same file).
The two files look somthing like:
artnr language description price
artnr ...

**9**

votes

**3**answers

2k views

### Is it possible to get the sheetname?

How can I get the sheet name in a cell? (I have more than one sheet, I want to get the current on each sheet)

**0**

votes

**3**answers

493 views

### If Column A matches Column D, replace it with Column E (same row)

This picture should explain what I mean:
I want to swap my orders that have IDs into their real names, like the second order table.

**1**

vote

**2**answers

225 views

### Creating an excel formula

I'm trying to create a formula (although I think it will need to be a macro).
The table has 2 columns, column one has percentages. What needs to happen is whichever 3 rows have the lowest percentage ...

**1**

vote

**5**answers

3k views

### Is there a way to tell which cells reference a selected cell in Excel?

In Excel, you can tell a cell to reference another cell in order to calculate its value. I'd like to be to do the opposite, to get any cells that reference the currently-selected cell. Is this at all ...

**0**

votes

**2**answers

1k views

### Query about Excel formula - Function IF(…)

I want to use the function IF() from Excel in a formula for some Cell, as shown below -
=IF(I2="SELL","(C2-F2)*D2","0")
Which means I want to see if Cell I2 has text- SELL in it (IF condition ...

**0**

votes

**1**answer

122 views

### Excel: How to use if in this situation?

I would like to do something like the following using worksheet functions.
If cell (A1 = "ABC"),
then append to the begin of the cell B1 "ABC:"
and
delete cell A1 and cell B1 ...

**2**

votes

**1**answer

721 views

### Excel conditional formatting on similar cells

We have a list of computers in an excel table, a few thousand. Most are named in the format of <3digit office code> - . For example, NYC-1234567. Sometimes, when machines get re-imaged, the ...

**3**

votes

**1**answer

1k views

### Request: Excel equivalent of Google Spreadsheet ARRAYFORMULA + CONTINUE functionality

Google Spreadsheets has a syntax permitting a user to specify an entire row, column, or area with a formula in a single cell. For example, entering ...
=ARRAYFORMULA(ROW(A3:A6))
... into cell C1 ...

**-1**

votes

**1**answer

987 views

### Group By, Sort, Count functions to keep running total

I have 3 part numbers with the same part name xyz, each with a quantity of 10 items. The items can be picked during the day or week, therefore changing the amount of items on hand.
I know I need to ...

**0**

votes

**1**answer

318 views

### Comparing two Excel Work sheets for re-used/non existing names

I have two excel worksheets that I need to find names that do not appear on one to the other. They are both very similar, however because of the added names a quick If true/false function will not ...

**1**

vote

**2**answers

142 views

### Is there any documentation of the behavior of built-in Excel functions called with array arguments?

(I originally asked this on stackoverflow. However, Excel formulas definitely straddle the line between "programming" and, um, "superusing", so I believe this cross-post is appropriate.)
Many of ...

**1**

vote

**2**answers

1k views

### Excel: What's an alternative to VLOOKUP that handles repeated lookup values?

VLOOKUP almost does what I need, but it won’t retrieve all the numbers associated with a list value that’s in the table multiple times. E.g. for table,
A 1
A 2
B
C 1
D 1
D 2
Is there an alternative ...

**2**

votes

**3**answers

223 views

### Getting the value in one column from a formula on another column in excel

I have an excel table that looks like this:
Date Original Actual
11/10/10 15 12
12/10/10 12 10
...
24/10/10 2 1
25/10/10 -2 ...

**26**

votes

**7**answers

104k views

### How to count number of distinct values in a range?

I've got a large table that is already organized using filters etc. I'd like to add a summary underneath certain columns that contain the number of distinct values in that column.
There's no ...

**0**

votes

**1**answer

211 views

### Excel Formula: trouble with the low % values not showing up in an in-cell chart? (image included)

I've a few tables that have % values like -4.5%; -16%; 15%; 6%; 2.5%. I'm interested in creating some in-Cell charts that show positive and negative values.
My smaller values aren't showing up, so ...

**3**

votes

**1**answer

458 views

### How can I disable the 'your formula has an error' pop-up in Excel 2007?

This is driving me absolutely crazy, while I'm in the middle of typing my equations into a cell I like to pre-create the parenthesis sometimes when order of operations is being explicitly set, however ...

**1**

vote

**3**answers

305 views

### check of date with functions lookup

i have input date in sheet1 say in A! 15/07/2010 and in another sheet range of dates with interval of six months in a1 01/01/2010 and in b1 30/06/2010 and c1 15 likewise data is there
i want to ...

**2**

votes

**1**answer

2k views

### Incrementing one value in an Excel formula

I have a formula in Excel that looks like this:
=VLOOKUP(posts!C12,users!A2:B1800,2)
I would like to duplicate this formula down the whole column, however, I would like C12 to increment (C13, C14, ...

**3**

votes

**1**answer

2k views

### Cross Referencing Data between Excel Sheets, plus a conditional statement

I have an Excel workbook with 2 sheets.
1st Sheet: Old User Data
2nd Sheet: New User Data
I want to add a new column to the 2nd sheet that contains the old user ID from the 1st sheet. To get this, ...

**1**

vote

**1**answer

321 views

### Summing custom labeled cells in Excel 2010

I have a sheet that I'm calculating particular costs. These costs have associated labels to them: Manufacturing, Variable, Both, Neither. I have created a drop down list for next to the costs that ...

**4**

votes

**6**answers

5k views

### In Excel, how to fill down consecutive integers following a pattern 1, 1, 2, 2,

In Excel, I know how to use the "fill down" feature to populate a column of sequential numbers.
I would like to do this but following a pattern like:
1
1
2
2
3
3
Does anyone know how to do this? I ...

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

**3**

votes

**2**answers

2k views

### Excel conditional formatting based on a formula

I would like to use Excel's conditinal formatting of a particular column based on values in two other columns.
I have values in columns A, B, C and D.
column A has text values (any value user ...

**10**

votes

**4**answers

27k views

### Can you do Regular Expressions in Excel without VBScript?

I'm looking for an Excel function that I can put in a cell to do a regex search of the contents of another cell. Is there anything available to do this? I'd prefer not to have to add VB Script to ...

**2**

votes

**2**answers

2k views

### Summing a column in the same column without a circular reference

In Excel you can sum a whole column with =SUM(B:B). But of course you can't use that formula in the column you want to sum, because you'll get a circular reference.
Is there an easy way of summing ...

**2**

votes

**2**answers

1k views

### Excel: ROUND() to the next unit of 5?

I'm creating a lot of graphs, so I'm making a common scale to be able to compare them using MAX/MIN values that I'm hiding in the graph as a separate series. My Vertical AXIS (Y) is in units of 5.
...

**0**

votes

**2**answers

810 views

### In Excel; Can LOOKUP return more than one cell as a result?

Lookup has two different usage:
Lookup( value, array )
and
Lookup( value, lookup_range, result_range )
I want to return more than one cell when it found the value.
Like:
...

**2**

votes

**2**answers

2k views

### How can I manipulate the result of VLOOKUP function?

If VLOOKUP doesn't find a match, it returns something that looks like "#__".
However, I want to manipulate the VLOOKUP formula not to write anything in the cell if it doesn't find a match. (This ...

**2**

votes

**3**answers

25k views

### How can I combine these 3 conditions in a single Excel formula?

I need to create an excel formula based on the following:
1. If Cell >= 500 Then the Cell text should be "Critical"
2. If Cell >= 400 Then the cell text should be "Medium"
3. If cell < 400 ...

**3**

votes

**4**answers

715 views

### Averages spread out over several fields in Excel?

I have a table with a sequence of data.
This data is used in a graph.
There are holes in this data that I want to fill in with smoothed out numbers.
Take this table for example:
In the example ...

**0**

votes

**1**answer

702 views

### Is there any way to make the formula bar in Excel 2008 on Mac OS X 10.6 dock or attach to the window instead of floating?

Is there any way to make the formula bar in Excel 2008 on Mac OS X 10.6 dock or attach to the window instead of floating?
So far everything I've read says no, but maybe you know the secret!

**2**

votes

**2**answers

779 views

### Excel: Counting the number of text strings

I have a column in a spreadsheet which lists the status of particular rows - the statuses are text strings like "accepted" "rejected" and so on.
Is there any formula I can use to count the number of ...