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

**0**

votes

**1**answer

852 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

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

**1**answer

245 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

449 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

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

**1**answer

669 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

900 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

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

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

494 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

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

**4**answers

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

**0**

votes

**1**answer

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

639 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

81 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

94 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

47 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

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

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

433 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

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

**8**

votes

**3**answers

11k views

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

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

**1**

vote

**1**answer

174 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

280 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

270 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

345 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

311 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

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

887 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

854 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

896 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

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

350 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

757 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

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

**1**

vote

**1**answer

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

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

**7**

votes

**1**answer

24k views

### Create a pie chart from distinct values in one column by grouping data in Excel

I have an Excel document containing information from a survey. I want to create a pie chart over location (countries).
How can I make Excel group all the distinct values together and then display ...

**0**

votes

**2**answers

130 views

### Excel Multiple Conditions Summing

I have Column A to A1600. In that column I have two project names "CP" and "GFI", in column B I have Project Phase 1-8, in column C I have the hours worked in each phase. The projects intertwine so ...

**1**

vote

**2**answers

467 views

### Multiple AverageIf in Excel

I have an Excel data sheet with lots and lots of rows that looks something like
C1 C2 Value
--- --- -----
A C 0.228
B D 0.234
A D 0.359
A C 0.125
...
I ...

**1**

vote

**1**answer

296 views

### How to calculate the total sum of hours with automatic filter

I have an Excel sheet where I sum hours to get the total amount of my work.
It works without problems, but if I add an automatic filter I'd like the calculations to be done only on the portion of ...

**2**

votes

**1**answer

103 views

### Formula error while counting unique entries in Excel

200 Pass
ABC 201 Pass
202 fail
300 Pass
DEF 201 Pass
202 Fail
I have a table which looks like the one shown above. The cells on the left ...

**1**

vote

**1**answer

666 views

### Excel database criteria, use string as input instead of multiple cells

In Excel, I got a small database. I want to use DMAX() to find a value in it.
+--------+---------+---------+---------+
| | Cost | | |
...

**1**

vote

**2**answers

707 views

### Why is there a date in my Excel time format, and how do I get rid of it?

I am using Excel at work and I have a list of users' online times.
I get a total say 180:30:00, which is 180hrs 30 mins and 0 seconds.
I need to keep this cell as the time, but I want to work out ...

**3**

votes

**3**answers

752 views

### Is there a way to set the name box of a cell programmatically in Excel

I would like to programmatically set the name box of a cell in a worksheet I'm working on, either through a formula or through a macro. The end point is that I don't want to set it by hand.
In my ...