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

learn more… | top users | synonyms (5)

3
votes
2answers
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
1answer
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
2answers
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
1answer
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
2answers
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
2answers
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
4answers
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
3answers
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
5answers
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
4answers
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
3answers
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
4answers
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
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
2answers
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
6answers
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
2answers
29k 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 ...
2
votes
3answers
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
3answers
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
3answers
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
3answers
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
3answers
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
4answers
781 views
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
3answers
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
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, ...
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
2answers
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
1answer
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
4answers
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
3answers
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
1answer
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
2answers
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
4answers
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
3answers
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
3answers
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
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 ...
2
votes
2answers
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
3answers
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
1answer
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
2answers
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
4answers
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
1answer
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
1answer
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
2answers
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
2answers
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
1answer
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
6answers
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
2answers
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
2answers
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
2answers
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