The worksheet-function tag has no wiki summary.
0
votes
3answers
35 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
39 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 ...
0
votes
2answers
49 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
51 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 ...
4
votes
2answers
111 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 ...
5
votes
1answer
51 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
0answers
60 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
1answer
87 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
83 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
98 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
79 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 ...
0
votes
0answers
25 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 ...
4
votes
3answers
111 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 ...
0
votes
1answer
75 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 ...
-1
votes
1answer
46 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
77 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
2answers
100 views
How can I count unique comma-seperated 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
63 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
106 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
49 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 ...
0
votes
1answer
35 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, ...
3
votes
1answer
102 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 ...
1
vote
2answers
107 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
92 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
47 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
51 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 ...
2
votes
1answer
102 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 ...
4
votes
3answers
411 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 ...
2
votes
3answers
59 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 ...
0
votes
3answers
140 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 ...
0
votes
1answer
94 views
How do I use an OR clause in a COUNTIFS formula?
This formula works, but it'd be simpler with an OR statement in there somewhere...
...
1
vote
1answer
46 views
How would I create a function in Google Docs (or Excel) to evaluate column a and return column b value.
I'm using google docs speadsheets to create a dashboard. I have two columns of data. I want to have a cell evaluate Column One for a specific name(for example, "Agency A") and return the value in ...
1
vote
1answer
29 views
In Excel, how do I highlight all rows that do not contain “,2”?
I'm verifying the FormatCurrency function is written properly in ASP code and I put everything in Excel from "find results".
I want to highlight only the ones that do not contain ",2" in them.
...
0
votes
2answers
26 views
copy worksheet to several workbook
I created a worksheet/spreadsheet with functions and references to cells in other worksheets/spreadsheets within same workbook to create a report of other worksheets within the same workbook.
I have ...
0
votes
1answer
52 views
How to extract the date of a cell stored as “dd/mm/yyyy hh:mm” format
I have a cell that holds a date/time value in the following format: dd/mm/yyyy hh:mm.
Now I need to extract the date from it as a string and append a different time to it. And I need the final ...
2
votes
2answers
65 views
2 columns: '1' as drop box for location and '2' auto populates with post code
In an Excel 2010 spreadsheet the user will select the location from the drop box. I need to have the correct post code automatically populate the adjacent cell. for example in cell A1 the user selects ...
1
vote
2answers
70 views
Converting “in place” a column of cells from minutes to hours and minutes in Excel
I have several columns of data in an Excel 2010 worksheet that are values in minutes and I want to convert them to hours and minutes (the data source will only generate an Excel workbook using ...
1
vote
1answer
48 views
Can Excel's ISERROR be used in a cascading manner?
Here is my original formula
=IF(ISNA(VLOOKUP($A3,INDIRECT("'"&G$2&"'!$B$3:$I$516"),6,FALSE)),"✖",
IF(VLOOKUP($A3,INDIRECT("'"&G$2&"'!$B$3:$I$516"),6,FALSE)=$D3,
...
0
votes
2answers
80 views
Can an array input be used in the sheet name of vlookup's range?
I have a formula which works, but is absurdly long, but repeats the core data over and over.
My spreadsheet contains 70 pages describing sets of cards. The page I'm working on gives me a particular ...
0
votes
1answer
127 views
COUNTIFS not counting blank cells as zero (when reference says it does)
I am building a function that tests the dates listed in four (4) columns. When testing each date in each column, one of my tests measures if the date occurs previous to the date that is being used as ...
1
vote
1answer
33 views
Hyperlink to a filtered pivot
Here's what I want to do.
I have a Pivot Table that contains data from more segments. Let's name them A, B, C and D.
I want to have an hyperlink that will open my pivot table directly showing just the ...
3
votes
2answers
74 views
How to choose a multiplier based on certain cell input in Excel?
I need some assistance with how to write an Excel formula for the below:
In cell A28 one of the following will be entered: LIN, LOX, LAR, CO2, GH2, GN2 or GO2
Depending on which is entered in cell ...
3
votes
1answer
68 views
Excel 2007 Function, last three items
My question is twofold, so bear with the wall of text. I'm making sort of a banking spreadsheet. I will input income/expenses in four columns (Date/Amount/Type/Description) and I need it to keep track ...
2
votes
3answers
120 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 ...
0
votes
2answers
131 views
How do I create a dropdown list for a column of constantly-changing values in Excel 2010?
I have an excel spreadsheet with a column for years, with a header:
Years
1993
1993
1994
1994
1994
...
2011
2011
There are duplicate values for the years, and additional rows will be added over ...
0
votes
1answer
62 views
COUNTIFS does not seem to count blank cells [closed]
Possible Duplicate:
COUNTIFS does not seem to count blank cells
I am trying to build a COUNTIF statement in Excel 2010 using a total of 4 sets of criteria. I would ideally like it to look ...
0
votes
2answers
43 views
Microsoft Excel adding up the number of YES's
In my Microsoft Excel file, I have a column like this:
YES
NO
YES
YES
YES
NO
YES
YES
In a separate cell, using an equation or something, I want to count up the number of YESs in that column. How ...
0
votes
3answers
36 views
Count rows in Microsoft Excel column
I have a table that looks like this:
Column A
Dog
Apple
And some more words. But on the side of the table, I want to have a cell which tells me how many rows I have in Column A, and I want ...
0
votes
2answers
47 views
Excel formula for extracting text
I have the following within cell A1:
NEW ORLEANS (0-0) at GREEN BAY (0-0)
I would like a formula to place in cell B1 "NEW ORLEANS" and in C1 "GREEN BAY.
0
votes
1answer
36 views
Table AutoExpansion incorrectly changes formula
I have a table in an Excel sheet where one column is calculated.
When adding a new row by typing, the table automatically expands, but wrongly changes the formula of that column in the row above. It ...
