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

**0**

votes

**2**answers

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

**3**answers

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

**1**answer

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

**2**answers

773 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

**1**answer

1k views

### Excel 2007: Array formula lookup using INDEX and MATCH is only looking at first row

I have a worksheet, "2011-11", with a series of numbers in column C. The numbers are each in a given currency, identified by a code in column B. What I want to do is write a single formula (in cell ...

**0**

votes

**2**answers

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

**1**answer

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

**1**answer

944 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

**1**answer

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

**1**answer

261 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

**2**answers

521 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

**1**answer

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

**3**

votes

**1**answer

850 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

**2**answers

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

**1**answer

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

**3**answers

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

**1**answer

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

**1**answer

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

**3**answers

553 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

**1**answer

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

**4**answers

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

**1**answer

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

**1**answer

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

**2**

votes

**1**answer

94 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

**2**answers

97 views

### Excel VLOOKUP Function

I'm using the VLOOKUP function and I have this function =VLOOKUP(B9,Products!A5:K29,11,FALSE)
It works but what do I need to add so the cell will display nothing if B9 is nothing? At the moment ...

**0**

votes

**2**answers

51 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

**1**answer

4k views

### OpenOffice Calc - how to insert BLANK in a formula?

In Calc, as in most spreadsheet applications, an IF() function exists that can be used to control behaviour, for example inserting this formula in cell C1.
=IF(A1>B1;"A";"B")
will display A in ...

**0**

votes

**1**answer

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

**2**answers

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

**2**

votes

**2**answers

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

**10**

votes

**5**answers

16k views

### Type math formulas in Microsoft Word the LaTeX way?

I wonder if there is some free solutions for typing math formulas the LaTeX way in Microsoft Word document (Office 2007)?

**1**

vote

**1**answer

178 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

**2**answers

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

**1**

vote

**1**answer

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

**1**answer

317 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

**2**answers

431 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

**1**answer

385 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

**4**answers

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

**1**

vote

**3**answers

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

**1**answer

952 views

### COUNTIFS does not seem to count blank cells [duplicate]

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

**2**answers

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

**1**

vote

**3**answers

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

**2**answers

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

**1**

vote

**1**answer

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

**1**

vote

**2**answers

1k views

### Excel doesn't automatically update ranges in SUMIF expression — why?

In Excel 2007, I have a worksheet arranged like this:
A B C
1 x 2 15
2 x 3 45
3 x 4 ...

**0**

votes

**2**answers

812 views

### Excel: two columns with rankings, create third with highest rankings

I have an Excel spreadsheet with columns A and B, each containing book titles, ordered by ranking (top to bottom), from two different sources.
I would like to know which items both sources consider ...

**0**

votes

**1**answer

966 views

### Convert a date in Excel from one format to another

How would I convert a format like this in Excel:
01.06.2007 21:48:16
To this:
Sat Jan 06 21:48:16 EDT 2007?
Assume these are US dates, so 01.06.2007 is actually Sat Jan 06 2007 and all my source ...

**0**

votes

**1**answer

1k views

### Use of array formulas within VBA

Excel array functions in worksheets are useful. E.g. for calculating the average of the sin values of an array A1:A5 I enter the following which works nicely:
{=AVERAGE(SIN(A1:A5))}
Now I would ...

**1**

vote

**1**answer

4k views

### excel 2010 vba macro ActiveCell.Formula = produces an empty cell when run

This macro formula (in a loop to place a Vlookup formula for each newly inserted worksheet) results in an empty cell when run:
ActiveCell.Formula = "=VLookup(""Total Other Operating Expenses"", ...

**-1**

votes

**1**answer

465 views

### Excel 2007 conditional formatting formula problem

I have the following problem. I have a sheet where the current month should be highlighted. For this I used the formula for conditional formatting. The formula for the first three months is like this:
...