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

learn more… | top users | synonyms (3)

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
705 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
684 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
766 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 ...
1
vote
2answers
1k views

Is there a way to give an Excel table a name that is worksheet-scoped instead of workbook-scoped?

When you create a table in Excel (I'm using Excel 2007) you can give it a name that shows up in the Name Manager dialog. However, the name has workbook scope. I'd like to have a table with a name ...
0
votes
3answers
2k views

Excel: How to sum values in a column based on multiple criteria

I have a list of criteria in row 20, like this: A20: "My Company, Inc." B20: "Client Expenses" C20: "Travel" The data is below, like this: B40: "Client Expenses" C40: "My Company, Inc." D40: ...
2
votes
2answers
1k views

How do I copy a worksheet without creating local copies of workbook-scoped named formulas?

I have a workbook with some workbook-scoped named formulas. When I copy a worksheet that has some (different) worksheet-scoped names, the copy ends up also having worksheet-scoped names that are ...
8
votes
5answers
38k views

What is the difference between function and formula in Excel?

What is the difference between those two when speaking of Microsoft Excel?
0
votes
1answer
704 views

Formula re-evaluation issues in MS Excel 2010 Beta

This has only started happening recently, where I have say a list of values: A B C _ _ _ 1| 1 4 5 [=sum(A1,B1)] 2| 2 5 3| 3 6 I have my formula in cell C1 [enclosed in ...
0
votes
1answer
95 views

How do I ensure only integers and with no duplicated values can appear in a column in Excel 2007?

How do I ensure only integers with no duplicated values can appear in a column in Excel 2007?
1
vote
4answers
3k views

How to input only unique values in a column in Excel 2007

I'd like to know the easiest way to make sure that only unique strings are entered in a particular column in MS Excel 2007? I know I have to put a formula in the Data > Data Validation, but I don't ...
0
votes
1answer
699 views

Excel Macro: compare separate workbook lists and LINK to the data in a corresponding column?

1. How can I compare two matching lists from separate workbooks? And then retrieve the data from the same row but another column - (Index&Match maybe be appropriate for this). But how does this look ...
3
votes
5answers
14k views

Excel - Delete specific word in each row?

Is there a way to have Excel delete a sepcific word in every row? For example, each row says Mr. and Mrs. {first name} {last name}, and I want to delete the {first name} from each one. (Or ...
1
vote
1answer
2k views

Excel Pivot: How can I show the % of the sub-total against the Grand total?

I have 15 colleagues who have answered 'numerous questions' over the years in a questionnaire. The questions used to be standardised and all colleagues had the same questions. Unfortunately standards ...
0
votes
1answer
178 views

Excel formula - sometimes array formula, sometimes not

I want to write some data to an Excel file, and I want the first page to show a summary of the data that are spread all over the sheets. The individual data items on a sheet can be included or ...
0
votes
3answers
328 views

Formula in Excel

Below is the data: A B C xxx 2 5 xxx 3 4 bbb 2 3 bbb 2.5 2 nnn 1.5 2 nnn 2 1 I want to ...
4
votes
1answer
7k views

Shortest Method To Reference A Single Row/Column of a Named Range in Excel?

I have a named range (NamedRange1) and I need to reference the first row and first column of that range within a formula. I came up with the following: First row: INDIRECT("R" & ...
0
votes
2answers
303 views

Excel: What formula combines this data into one COUNT amount?

I have 30 colleagues who are answering questions over 3 time periods. Each has their own Excel workbook with the questions, and over the year they update it. I collate their worksheets into one master ...
0
votes
2answers
625 views

excel - merge cells including a zip code

I have the need to merge a bunch of cells that comprise an address (street, city, state, zip) into a single cell. No problem except with the zip code. The zip cell has only 4 digits for any zip ...
0
votes
1answer
504 views

Excel, Lookup special characters and spaces

I have an excel, spreadsheet that has multiple sheets. The first sheet is an index of files, I am using the following forumla to look up a value in column A, references against the index sheet, if it ...
5
votes
4answers
23k views

How to calculate hours and minutes between two time periods?

I’m working on a very simple time sheet for my work in Excel 2007, but I have run into trouble about calculate the hours and minutes between two time periods. I have a field: timestart which could be ...
1
vote
2answers
4k views

EXCEL: How can you FORMAT a serial number into a MONTH ( Jan, Feb)

I extract the Month() number out of my date. I now want to change that number into a recognisable string Jan, Feb, Mar, etc. In Access you can use FORMAT, but what can we use in Excel? E.g. ...
0
votes
2answers
1k views

Specify formatting information in Excel forumla

Is there a way to specify the formatting in an excel formula? =IF(A1="Large","This is BIG","This is small") Where "BIG" is of a font size larger than the text for "small"? (I'm aware of ...
1
vote
2answers
7k views

Use cell formatting (e.g. “Good”, “Bad”, “Neutral”) in formulas?

I am compiling a comparison of different pieces of software in an Excel spreadsheet. It is a big long list of features (the rows), with each column being one of the applications I'm evaluating. I've ...
4
votes
5answers
15k views

Using excel, how can I count the number of cells in a column containing the text “true” or “false”?

I have a spreadsheet that has a column of cells where each cell contains a single word. I would like to count the occurrences of some words. I can use the COUNTIF function for most words, but if the ...
1
vote
2answers
88 views

I hyperlinked a cell in excel 2003, formula issues?

I have a budget spreadsheet using excel 2003. I have My deposit, then all of my bills, the total, then a cell that has the difference(between the amount of deposit and the total of the bills). The ...
0
votes
1answer
293 views

MS Excel convert cell matrix (1x15 -> 3x5)

What is the formula for displaying cells in a 3 cell increment at a time, like A1, A4, A7, A10? Moreover, would it be possible to transform below a list (1x15) of data into a table(3x5). Thanks. ...
1
vote
1answer
4k views

Excel cell references not updating when referenced cells are sorted

There are two tables, each with 75 entries. Each entry in the 2nd table calls an entry in the first table a parent. One of my 2nd table columns contains the "Parent Price", referencing the Price ...
0
votes
2answers
3k views

Excel Sum that excludes duplicates

I have data in the following format: Id Value ------------ 1 20 2 40 3 20 3 20 4 50 I want to sum the value column while excluding duplicates from the id column. In this ...
9
votes
1answer
11k views

allow two possible values in a Excel cell

I would like to create a cell format for one cell to only allow the value "yes" or "no" for this cell. How could I do this?
0
votes
2answers
260 views

Excel Matching problem with logic expression

I have a block of data that represents the steps in a process and the possible errors: ProcessStep Status FeesPaid OK FormRecvd OK RoleAssigned OK CheckedIn Not Checked In. ...
0
votes
2answers
668 views

Spreadsheet formula: lowest 100 values in a range

Is there any way I could sum up the lowest 100 values within a range? I know that min() would give you the lowest value but i need something to return the 100 lowest values. I just used 100 ...
1
vote
1answer
2k views

Excel: Summing values within a certain date range (> start date and < Finish date)

How do I include a 'Between these dates' criteria inside a SUMPRODUCT? =Sumproduct((RangeName=Cell A1)*(RangeDate=Between 01/03/10 and 31/03/10)*(RangeCost)) What's the way to manage dates ...
0
votes
1answer
780 views

Pivot tables: How can I total the subtotal?

My SUBTOTAL row shows Person A needs £115, Person D £234 and Person G £789, but how do I SUM those totals and get it to show the total on the same ROW? The Rows are subscription names. The Value ...
2
votes
2answers
5k views

How can I change a cell's background color based on the cell's current value?

I have a column with function values of True or False: =IF(ISNA(A1), false, true) from row 1 to 5000+ rows. I would like to mark all the False as red background color or text as red. Is there any ...
2
votes
1answer
517 views

Excel: ROUND & MOD giving me strange DATE results

This is sort of related to a previous question. My formula, which seemed to work fine yesterday now gives strange results. Today is the 30th of March (30/03/10). It's 10:11am on the clock that the ...
1
vote
2answers
2k views

Excel 2007: how to work out percentages of groups (top 10% of…)

I've recently read the following paragraph, and wondered: how you would organise the data (possibly Column A = country, Column B = salary, Column C = tax paid) but what formulas/calculations are ...
4
votes
1answer
2k views

Excel 2007 - How can I write “use this cell” or IF BLANK “use this cell”?

I am trying to show the Days between NOW() and the dates (dd/mm/yy) in, either Column B or Column C - depending which one is NOT blank A B C 29/03/10 01/04/10 29/03/10 ...
1
vote
1answer
1k views

Excel: VLookUp solves a potential Nested IF problem

I manually enter numbers on one cell according to text values in the cell adjacent to it. Is there a way to use the IF function to help me manage this? The text is automatically generated with a ...
1
vote
1answer
756 views

Concatenate cells that change daily automatically?

I use concatenate to pull data together from different cells in my spreadsheet. Since my data changes daily, I want the formula to also change daily without having to manually input the new cell in ...
2
votes
2answers
3k views

How can I write an excel formula to do row based calculations?

Could someone please help me with the following excel formula. Each row in my spreadsheet contains details about a product. I have one column 'Quantity' (Col B) and 'Profit' (Col F). I want to use ...
3
votes
5answers
28k views

Excel: copy cell formatting in equation

If I make an equation: ' on cell A2 =A1 Is there a way to make A2 have the exact same formatting as A1 and not just the value? EDIT: i.e. I want a FORMULA that copies both the value and the ...
2
votes
2answers
13k views

How do you include an OR inside a SUMPRODUCT function, in Microsoft Excel?

How do you include an OR inside the sumproduct function in Excel? I currently use two sumproduct formulas because I have two variables I want it to count: ...
0
votes
3answers
20k views

Excel 2007 formulas not autofilling when I drag down

If you write a formula like: =sumproduct((something=$A1)*(somethin=$B1)) Giving a result of 8. Usually the references and results change as you drag it down into other cells. Well,the references ...
0
votes
2answers
672 views

How to make an excel formula which totals several agecent rows based on cell values

I have an excel sheet with three columns: date, person and percentage. I would like to put in a data validation that flags cells if the total for a given data/person combination do not equal 100%. Is ...
0
votes
1answer
609 views

Ctrl + F9 Does not update Formula Text on Excel 2009

I have a lot of excel workbook that follows a template. Since each workbook has a lot of sheets. I usually just use the equal formula to refer to the heading of the first sheet for the succeeding ...