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

learn more… | top users | synonyms (3)

0
votes
1answer
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
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
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
2answers
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
1answer
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
1answer
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
2answers
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
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
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
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 ...
0
votes
1answer
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
1answer
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
1answer
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
2answers
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
2answers
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
1answer
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
1answer
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
2answers
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
2answers
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
3answers
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
1answer
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
2answers
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
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
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
2answers
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
1answer
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
4answers
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
3answers
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
1answer
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
2answers
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
3answers
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
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
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
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
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
1answer
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
1answer
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
1answer
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
1answer
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
2answers
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
2answers
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
1answer
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
1answer
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
1answer
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
2answers
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
3answers
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 ...