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

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

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

**1**answer

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

**2**answers

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

**2**answers

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

**3**answers

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

**2**answers

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

**5**answers

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

**1**answer

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

**1**answer

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

**4**answers

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

**1**answer

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

**5**answers

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

**1**answer

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

**1**answer

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

**3**answers

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

**1**answer

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

**2**answers

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

**2**answers

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

**1**answer

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

**4**answers

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

**2**answers

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

**2**answers

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

**2**answers

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

**5**answers

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

**2**answers

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

**1**answer

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

**1**answer

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

**2**answers

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

**1**answer

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

**2**answers

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

**2**answers

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

**1**answer

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

**1**answer

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

**2**answers

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

**1**answer

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

**2**answers

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

**1**answer

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

**1**answer

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

**1**answer

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

**2**answers

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

**5**answers

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

**2**answers

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

**3**answers

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

**2**answers

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

**1**answer

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