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

learn more… | top users | synonyms (3)

-1
votes
2answers
721 views

Microsoft Excel - Generating random time invtervals

I have a list of employees around 30. I need to create a simple table that will assign them a specific time that they are to be on the phone and 24 hours later excel will automatically(randomly) ...
1
vote
2answers
1k views

Count negative values within date range (month)

I have values in a table like this Date Values 01/01 5:35 02/01 -7:08 02/01 3:45 15/01 -4:05 02/02 3:25 13/02 4:25 19/02 6:20 I would like to count by month how many 'Values' ...
1
vote
2answers
6k views

Excel get cell value which is NOT Match in a row

I would like to search C2:I2 which is the cell value NOT equal to "=No=", which is "ABC", and the formula I would like to store in A2. I was able to look for some formula which works with MATCH(), ...
2
votes
1answer
560 views

Excel formula - getting mapping details from 2 sheets of the same work book

Please can you help me with the below excel formula? Sheet1: Col A = Business use case number (BUC001, BUC002...) Col B = Business use case definition corresponding to the Business use case number ...
0
votes
2answers
352 views

How to round up number in Excel

Let's say we have an array of nubmers $9,333.00 $5,733.00 $21,333.00 $21,833.00 How can we make them round up to $9,500.00 $6,000.00 $25,500.00 $22,000.00
6
votes
4answers
40k views

How to combine values from multiple rows into a single row in Excel?

I have a data dump in Excel that consists of annual customer data for two different values. The data was provided with a separate row for value for every year and customer. I.e. it looks like this: ...
2
votes
2answers
3k views

Excel VBA Function for Value AND Format Lookup

I need a custom function to lookup both a cell value and copy the source format. I have a list of values in two columns. The first is of integers and the second column is the corresponding text ...
0
votes
4answers
2k views

Excel - Smooth a curve by calculating / interpolating source missing values

I have a table of values like this one : 18 18 18 46 46 46 46 46 57 57 57 57 57 57 69 69 69 69 69 69 64 64 64 64 64 64 56 56 56 48 48 48 44 or if you prefer : 18 0 0 46 0 0 0 0 57 0 0 0 0 0 69 0 0 ...
0
votes
1answer
253 views

Lookup is failing to accurately use the lookup value

I am using two Excel workbooks; one is the backend for the other. In Book 1, there are sheets sheets with a $Timeline, $ClientInfo, and $VendorInfo. Book 2 is meant to be a summary record of different ...
1
vote
1answer
1k views

MS Excel 2010 - Unprotect Grouping Controls

I have an Microsoft Excel 2010 spreadsheet that I want to share with my team on a read-only basis; I want to prevent them from modifying it, so that I keep control of changes. It's quite large, and ...
1
vote
3answers
4k views

Microsoft Excel - Count the number of values each name has in a single column

I have a CSV file containing 100,000 rows. Each row consists of information under a number of columns, such as "Date", "Page Accessed", "Page Type", "IP", "User Surname", "User Forename", "User ...
1
vote
1answer
2k views

Prevent Excel 2010 user from pasting formatting into cell

Is there a way to lock a cell so that a user can copy/paste a value into it, but not copy formatting to it? Locking the worksheet doesn't prevent the user from changing the formatting of the cell by ...
2
votes
3answers
386 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 ...
0
votes
3answers
233 views

Excel: Find rows with first column empty

My company uses a giant excel spreadsheet that shows which IP Addresses are being used by which computers (among other information). IP Addresses that are not in use still have a row, but the ...
1
vote
1answer
638 views

merge cells with formula skip duplicates

Our Goal is to merge the names (B) when the data in A is equal. The formula below looks at the next line (A), if it's the same, it merges data (name) from B. The formula is in C, so the result of the ...
2
votes
3answers
681 views

Advanced cell selection in Excel

I am new to this flavor of StackExchange, so if this belongs elsewhere, please move it; I figured this would be the best place, though. I am making an Excel Worksheet that simply stores basic ...
2
votes
2answers
6k views

Excel: How to return the last Row Number that a contains a specific string?

In excel, is there an excel formula I can use to populate Column B (in my example below) that will return the last row number that contains a specific string, before the current row? The data is not ...
0
votes
1answer
208 views

Excel Pivot Table Calculated Field including LEN

I'd like to setup a pivot table field that counts the number of characters in a given field. LEN([field name]) only returns 1 for every row.
5
votes
2answers
2k views

Excel: Counting number of MAX values in a range

I am using Excel 2003 (version limited due to corporate policy) to keep a score sheet: A B C D 1 Round | Rita | Sue | Bob ------+------+-----+----- 2 1 | 5 | 3 | 4 3 ...
7
votes
1answer
4k views

Get column number of an address in Excel

In cell LJ131 I have written the text string "$HG$86" (w/ out the quotes). I need to get the column number of the address value represented by that string, $HG$86. Everything I've tried thus far ...
2
votes
1answer
3k views

Lookups targeting merged cells - only returning value for first row

I have a master worksheet which contains data that I wish to link to another 'summary' sheet using a lookup. However, some of the cells whose data I wish to include in the summary sheet are merged ...
0
votes
2answers
204 views

Conditional Formatting Non Contiguous Ranges

I have a large spreadsheet, which came prepopulated with data. What I would like to do is the following. If I select I7:15, and apply this conditional formatting Cell Value is equal to =G7, colour ...
1
vote
1answer
835 views

Calc - How to combine rows from multiple sheets into 1 main sheet

I have several statements separating my various bank accounts in Calc, they all use the same columns and have a Date column which I sort them by. I want to create a 'Overview' sheet which contains ...
1
vote
2answers
1k views

Why am I getting wrong row with INDEX function?

This image is of my data. I am running an INDEX method to get the invoice number. It is working on all other rows but this one although they all have the exact same formula. I eventually found ...
0
votes
2answers
952 views

Remove duplicate rows in excel when not all the columns are the same

How can I automatically remove duplicate rows in Excel when data->filter doesn't work, because the columns aren't all the same? I believe the picture explains what I need, but I can probably supply a ...
1
vote
2answers
4k views

VLOOKUP when Lookup Value is a cell populated via another formula

I need to perform a VLOOKUP, but the Lookup Value is a cell that is populated based on an other formula. The lookup is bringing me back a value of "#N/A". When I recreate the same values in another ...
1
vote
1answer
989 views

Obtain value from another column based on selected list value

Say I have two columns, the currency and the exchange rate and on another sheet in the same workbook, I'm referencing the currency as a drop down list. I want to be able to pull the value of the ...
7
votes
3answers
6k views

What is a formula to remove the whitespace from an Excel cell when trim doesn't work?

I have a table of Excel data that I copied from an html table in Internet Explorer. A column of table values all seem to have a space at the end of them, but Trim won't remove it. When I paste-special ...
7
votes
4answers
30k views

How to search for newline or linebreak characters in Excel?

I've imported some data into Excel (from a text file) and it contains some sort of newline characters. It looks like this initially: If I hit F2 (to edit) then Enter (to save changes) on each of the ...
-2
votes
2answers
201 views

Simple Line Graph in Excel

How can I make a graph with this data: Club distance club speed 9 iron 85.8 71.7 9 iron 89.8 68.6 9 iron 85.2 69 5 iron 132.5 73.6 5 iron 131.3 ...
0
votes
2answers
3k views

Pass values instead of range to VBA Function

I am creating VBA functions in Excel 2007. One has a prototype: Function ToLevelCode_Range(val As Double, ByRef R As Range) I can call it this way just fine and it gets the correct result: ...
1
vote
3answers
2k views

How can I count unique comma-separated values in Excel 2010

I need to build a formula to walk a column of comma-separated values and count the number of unique values seen. I am using Excel 2010. I have a screenshot of some example data... The output of ...
2
votes
1answer
3k views

Excel 2010 formula to strip out part of cells

I have used basic IF ISNUMBER functions before to identify parts of cells and return the value in a different cell, such as: ...
1
vote
2answers
710 views

Excel: include formatting in formula of another cell

I have a spreadsheet which contains a hyperlink based on the contents of another cell. I need the hyperlink to be based on the display (formatted) value rather than the data value of the source cell. ...
0
votes
2answers
995 views

How do I get Excel to automatically replicate functions across the spreadsheet?

I have a spreadsheet where I'm doing a whole table of calculations. I'm playing with the calculations and seeing what it does to the data. I expect to change the calculations dozens of times (at ...
3
votes
1answer
9k views

AutoFill Large Number of Cells in Excel?

I have a formula in Excel that I would like to autofill to 999,999 other cells. How can I do this without dragging the autofill handle down the entire sheet?
0
votes
1answer
880 views

how to find cells that have a certain text in open office's calc (excel might work also)

I need to calculate averages on my expenses spreadsheet, so I figured out I could do something like this: - get all cells that have the text "electric bill" - get the cell at the right of that one, ...
4
votes
1answer
4k views

Using INDIRECT and AND/IF for conditional formatting

I am having some issues with using INDIRECT to apply conditional formatting. I have column G which carries a percentage. I want to conditionally format column H based on the percentage being greater ...
5
votes
1answer
246 views

Excel formula needed

I'm not so good in excel but i "think" my problem can be solved using excel.. I've got a column with numbers and i'd like to find the best combination of using these numbers. In our case the best ...
1
vote
2answers
458 views

Filter and reorder columns in Excel

I've got a service (phplist, a newsletter manager) that exports a user list with several fields. At the end of it, each user has one or more lists to which he has subscribed. The problem is, that ...
2
votes
1answer
5k views

What excel formula can I use to calculate the 2nd Monday of a given month?

What excel formula can I use to calculate the second Monday of a given month? You can assume I have a cell containing the first day of the month to work with. In the interests of other users, can ...
2
votes
1answer
701 views

Populating cells with data from another spreadsheet after just keying in a few letters

I have 1 workbook with 2 spreadsheets. Spreadsheet 2 column A contains a long list of company names, Columns B - H contain critical information about the company. Spreadsheet 1 contains all of the ...
0
votes
2answers
941 views

How to assign row numbers to cells in Excel

I want to make the columns be from 1 to 195, in order from least to greatest, so that if I make a mistake (say, I want to add an entry between row 28 and 29), I want Excel to order itself from 1 to ...
3
votes
1answer
1k views

How to set 2 conditions / criterias for VLOOKUP / LOOKUP / etc in OpenOffice Calc (or Excel)

I have this spreadsheet that started as a silly aid for a game (Mafia Wars 2), but grew into a tricky spreadsheet question. In the game your character have 9 "slots" for weapons and armors, 1 for ...
5
votes
3answers
10k views

Find missing entries from a list of sequential numbers in a column?

I have an Excel sheet containing the results of 66000 test cases. Or, at least it should... Now, because I was running the tests asynchronously and kept stopping and starting I had the foresight to ...
0
votes
1answer
1k views

How to convert decimal years into a date (format dd-mmm-yyyy or similar) in either Excel or SPSS?

I've found a fair number of formulas online which let me convert birthdate into decimal age in years but I can't find something that converts, for instance, 1.0 into 04-Nov-2011. Any help would be ...
-2
votes
1answer
135 views

How do I create a range from a cell value?

For example: A B 7926 3 FROM =COUNTIF(AQ$7926:AQ7970,"=0") 7926 3 FROM =COUNTIF(AQ$7926:AQ7971,"=0") 7926 3 FROM =COUNTIF(AQ$7926:AQ7972,"=0") 7926 3 ...
2
votes
3answers
507 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 ...
1
vote
1answer
2k views

Conditional Data Validation

I've been taking a certification course on Excel. It's a beginner class but something caught my attention. Specifically, Data validation. We learned how to do it using the default conditions but I ...
1
vote
4answers
11k views

Excel formula to convert per-annum interest rate to compounding daily and weekly rates

If I borrow $100,000 at an annual interest rate of 10%, then I would have been charged $10,000 at the end of one year. However, I want the interest to be calculated daily and compound. If I simply ...