Tagged Questions

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

learn more… | top users | synonyms (5)

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
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
1answer
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
2answers
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
1answer
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
2answers
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
1answer
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
1answer
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
1answer
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
1answer
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
2answers
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
1answer
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
1answer
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
2answers
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
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
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
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
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
1answer
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
4answers
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
1answer
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
1answer
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
1answer
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
2answers
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
2answers
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
1answer
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
1answer
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
2answers
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
2answers
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
5answers
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
1answer
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
2answers
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
1answer
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
1answer
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
2answers
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
1answer
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
4answers
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
3answers
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
1answer
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
2answers
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
3answers
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
2answers
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
1answer
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
2answers
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
2answers
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
1answer
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
1answer
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
1answer
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
1answer
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: ...