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

**3**

votes

**2**answers

4k views

### Sum contents in column from starting cell on down without setting an explicit last cell index

I have an Excel spreadsheet and would like to sum the numbers in a column starting at one cell (say, B5) and continuing down to the last number in that column.
I know I could just enter a large ...

**3**

votes

**1**answer

503 views

### Toggled Hyperlink with If:Then formula [duplicate]

I'm attempting to make a formula that will toggle a hyperlink on or off depending on the argument. Basically, I want to make it argue the following:
If there is text in (Tab A, Cell A), then create a ...

**3**

votes

**2**answers

143 views

### Flatten Excel Formula (2007)

Say cell A1 has the formula A2 + A3
and B1 has the formula B2 + B2
and C1 has the formula A1 + B1
I need an easy and quick way to create the formula for C1 in the flattened state..
i.e.
(A2 + A3) ...

**3**

votes

**1**answer

475 views

### Excel: Why is VLOOKUP returning inconsistent and unexpected results?

I'm automating an Excel column to assign account values based on the vendor paid. For instance, if AT&T gets paid, the account value is Utilities:Telephone, and when the post office is paid, the ...

**3**

votes

**2**answers

405 views

### Convert “Mon Aug 01 09:08:25 CDT 2011” to a usable date/time using Excel

I have a giant spreadsheet with computer names and the date stamp that they last reported into our PGP server that I need to convert to a usable date/time, so I can sort the column by the time the ...

**2**

votes

**2**answers

6k views

### How do I get “yes” or “no” in Excel if the information comes from “true” and “false”?

first of all, I have to point out that I am a German visiting this forum (I couldn't find anything useful on the German websites), so excuse my English if it is bad here and there ;-)
Here is my ...

**2**

votes

**4**answers

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

**2**

votes

**3**answers

479 views

### How to update all functions in Excel 2007?

Is there a way to quickly fill down a column of functions. For example, if I change my function from
=3*B2
And then fill down so it does it for a lot of rows.
But then change this column to:
...

**2**

votes

**5**answers

1k views

### Does excel have an arrow key shortcut to skip blank cells?

In most versions of Excel (I'm using 2010), pressing ctrl+up or ctrl+down will take you to the edge of the current data region or the start of the next data region in a worksheet. Effectively skipping ...

**2**

votes

**4**answers

22k views

### Excel 2010: Add text to each cell of a column

How can I add @gmail.com or something similar to a column of usernames without having to type this into each cell? I am using Excel 2010. I have a list of several hundred usernames that need to have ...

**2**

votes

**3**answers

171 views

### Excel 2010 input blank for all 0 results

I use this
=IF(MINUTE(E59)+(SECOND(E59))=0,"",(HOUR(E59)*60)+(MINUTE(E59)+IF(SECOND(E59)<30,0,1)))
To calculate time into minutes like 00:06:13 = 6 as a result and 00:00:00 = blank as a ...

**2**

votes

**4**answers

3k views

### Can one list everything that uses a particular cell in Excel 2010?

I've been tasked with making changes to an Excel 2010 spreadsheet I know little about.
One of the biggest changes the client wants is to remove numerous cells that are no longer needed by his ...

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

**2**answers

1k views

### Calculate average score for by category in Excel

Assume I have the following 2 sheets in an Excel workbook:
Sheet companies:
A B C
1 COMPANY COUNTRY SCORE
2 Foo Greece 10
3 Bar Greece 2
4 Baz Greece 3
5 ...

**2**

votes

**6**answers

30k views

### How to prevent Excel rounding numbers or adding redundant 0's?

I have a column of numbers that appear like this:
but the actual value of the shown cell is 20130.153334
Other values have a different number of decimal places. I don't want to add redundant 0's, ...

**2**

votes

**2**answers

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

**2**

votes

**3**answers

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

**2**

votes

**3**answers

271 views

### ,“>”&0),“”) at the end of a countif formula

I have the below formula, but don't understand why the last part of it ,">"&0),""). Can somebody clarify pls?
...

**2**

votes

**3**answers

8k views

### Sum of values from rows if criteria found in horizontal range

I have a spreadsheet with a number of row records containing country/value pairs (A2:C11) and row records representing regions listing those countries horizontally (14:16):
What I would like to do ...

**2**

votes

**3**answers

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

**2**

votes

**3**answers

4k views

### How do I `SUM` by multiple columns in Excel

I have a comma delimited file that includes two columns date/time (which imports as Excel's mm/dd/yyyy hh:mm custom format) and status of 1 or 0. The status represents a piece of equipment either ...

**2**

votes

**4**answers

781 views

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

**3**answers

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

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

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

**2**answers

6k 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

3k views

### SUMPRODUCT with condition

I have 2 columns B="Points" and C="Grade"
And i'm trying to find the formula to sum the products of all points*grade for grades >= 60.
I tried this formula with many others, with no success:
...

**2**

votes

**4**answers

371 views

### Robust way of creating formulas in Excel

To simplify the problem, let's say I have an Excel sheet which has different numeric values in the cells A1 to B100 (two columns by 100 rows). In column C, I would like to have a formula that will ...

**2**

votes

**3**answers

355 views

### Excel formula to calculate power of [closed]

Given the following equation: 2^X - 2 = N
I'd like to solve for X. N is known.
What function could I use in Excel to do this?
Thanks in advance.

**2**

votes

**1**answer

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

**2**

votes

**2**answers

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

**2**

votes

**4**answers

6k views

### How do I calculate number of hours > 24 in Excel?

I've got an excerpt of my spreadsheet below. It's a time tracking application containing a start date/day, a session ID and the duration of the session. Column A is populated by adding the duration in ...

**2**

votes

**3**answers

416 views

### Update a summary sheet in Excel

I have a spreadsheet that will eventually contain 52 sheets(one for each week) On each sheet there is a cell with a percentage in it. I would like to copy this cell to a summary sheet so that it can ...

**2**

votes

**3**answers

591 views

### How to get a de-duplicated list of row labels and their summed values in Excel 2010

I have an Excel sheet with multiple rows in this format:
Column A|Column B
label | value
--------|--------
header (merged cells)
labelA |12
labelB |5
labelC |9
header (merged cells)
labelA ...

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

**2**

votes

**2**answers

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

**2**

votes

**3**answers

36k views

### How to set Function “formula” for whole column in Excel 2007?

I want to set "Formula" to all cells in certain column whatever the number of rows was.
But i don't want to set this function to first cell "Header" of this column.
How do i make it ?.

**2**

votes

**1**answer

68 views

### What would be the the mathematical equivalent of this excel formula? =PMT()

I need to create a javascript form that does the same calculation as this =PMT() function.
mortgage = 220000
rate= 4.75%
term = 30
The example formula I have is =PMT(4.75%/12,30*12,220000*-1)
What ...

**2**

votes

**2**answers

46 views

### dragging to populate cells in Excel

I am aware that you can fill the cells in Excel as a series by dragging the square in the bottom right corner.
for example typing "1" in Cell "A1" and dragging the box down to "A10" will give you a ...

**2**

votes

**4**answers

5k views

### extract date from string in excel

How can I extract date from such strings in excel?
John8/11/12
Jackson09/11/2013
Desired result:
John 8/11/12
Jackson 09/11/2013
Can I do this using only excel formula using excel ...

**2**

votes

**1**answer

242 views

### Excel =+ formula impact

I can't for the life of me remember but what does putting + after = in a formula actually do?
For example the difference between:
=+VLookup(A1,B1:B10,1,FALSE)
Compared with:
...

**2**

votes

**1**answer

94 views

### MS Excel - splitting a formula into individual cells?

I'm not sure if this is possible, or if I'll have to do it manually, but I have lots of cells in the following format:
=87.12+56.52-16.50+98.21-9.51
If possible, I'd like to break it up into ...

**2**

votes

**2**answers

2k views

### “Fill Weekdays” in excel 2010 fills sunday to friday

I have an excel sheet, in which I filled the date 01/03/2013.
I used the autofill feature to fill in the weekdays only, but excel filled Sunday to Friday, instead of Sunday to Thursday.
Any way to ...

**2**

votes

**2**answers

1k views

### How does one find the definition of a function in Excel 2010?

I've been tasked with making changes to an Excel 2010 spreadsheet I know little about. I was pretty good with Excel about a decade ago, but I'm not really sure how to look-up the definitions of ...

**2**

votes

**1**answer

1k views

### Split Address in Excel Cell

I have an Excel column where each cell contains the contents of an address. What I'd like to do is split THE FIRST TWO words into a their own separate columns.
For example:
123 South Bend Rd, Make ...

**2**

votes

**6**answers

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

**2**

votes

**2**answers

8k views

### What's the difference between “vlookup” and “lookup” in Excel?

What is the main basic difference between VLOOKUP and LOOKUP in Excel?
I read the main point is that you can specify a range of columns in VLOOKUP and then pick one to return the value. However, it ...

**2**

votes

**2**answers

142 views

### How to concatenate values in Excel?

I have a spreadsheet that looks something like this:
A B C D E ...
1| 1 0 0 1 0
2| 0 1 0 0 1
3| 0 0 0 1 0
4| 1 0 0 0 0
I'd like to concatenate row 1 in column F so ...

**2**

votes

**2**answers

351 views

### Convert a formula in Excel into an Array Formula without Ctrl-Shift-Enter?

Question: How might it be possible in Excel to convert a formula into an array formula, without using a hotkey?
Normal Formula
Array Formula