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

learn more… | top users | synonyms (3)

3
votes
1answer
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
2answers
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
1answer
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
2answers
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
5answers
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
2answers
25k views

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

I'd like to display an empty box (HTML entity &#9633 □ 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
4answers
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
4answers
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
1answer
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
4answers
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
1answer
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
3answers
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
1answer
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
1answer
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
1answer
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
1answer
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
2answers
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
2answers
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
3answers
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
3answers
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
2answers
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
5answers
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
2answers
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
1answer
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
1answer
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
1answer
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
1answer
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
1answer
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
2answers
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
2answers
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
3answers
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
7answers
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
1answer
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
1answer
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
3answers
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
1answer
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
1answer
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
1answer
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
6answers
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
3answers
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
2answers
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
4answers
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
2answers
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
2answers
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
2answers
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
2answers
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
3answers
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
4answers
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
1answer
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
2answers
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 ...