All Questions
Tagged with worksheet-function spreadsheet
223
questions
81
votes
9
answers
328k
views
How to create a formula for every row in a column in Google SpreadSheet?
I use three columns. A, B and C.
In column C I have a formula every row =A1*(1.6*B1) and then for the next row I have =A2*(1.6*B2) in C2.
How can I do so I don't have to type in the new formula in ...
11
votes
7
answers
34k
views
Using excel, how can I count the number of cells in a column containing the text "true" or "false"?
I have a spreadsheet that has a column of cells where each cell contains a single word. I would like to count the occurrences of some words. I can use the COUNTIF function for most words, but if the ...
10
votes
2
answers
26k
views
How do I test if a cell in OpenOffice Calc contains #N/A?
I'm using the VLOOKUP function to lookup values in a column based upon a search query. But since it's not a given that a value always exists for my search parameter, sometimes VLOOKUP returns #N/A.
...
9
votes
3
answers
50k
views
Cannot delete created excel table
I have created an excel table. However, I now want to delete or change it because my range changed.
However I can't delete it in the excel Name Manager:
Any suggestion why I cannot delete it?
Would ...
8
votes
4
answers
9k
views
Formula to see if a series of numbers is increasing in value?
I have a spread sheet with thousands of sets of numbers. That is too say, there are ten columns and each column has a number - and there are thousands of rows of these numbers.
What I want to do is ...
8
votes
3
answers
9k
views
How to compare two Excel spreadsheets?
I would like to compare two Excel spreadsheets
Both have the same columns, in the same order, but not the same rows.
I have a spreadsheet with 1,000 rows and 50 columns including a unique ID for ...
7
votes
5
answers
3k
views
Excel Formula to Calculate Duration of Time Based on a String of Text Such As 9:00am — 12:30pm
I'm trying to develop an Excel formula that would calculate the actual time duration for a string of a time range of the following format: 1:00am – 3:00am
According to this article it seems it should ...
5
votes
1
answer
18k
views
ADDRESS function nested in SUM returns Err: 502
When working on one problem, I came upon something that confuses me and was hoping for some clarification.
I am trying to use the SUM() function in a very round about way.
The formula
=SUM(ADDRESS(...
5
votes
1
answer
46k
views
Is there a way to delete everything except what is selected in Excel?
I have an Excel template with 20 tabs (worksheets) and plenty of data in each sheet. When a user opens a copy of the template, he will only need to use one tab.
Is there a sneaky way to select that ...
5
votes
4
answers
6k
views
Excel annoyance: How to maintain formulas in a ledger?
A common use of Excel is to create a spreadsheet which functions as a transaction ledger of some kind, where one or more fields in each row is calculated by adding a value or values from the current ...
4
votes
2
answers
30k
views
What's the difference between "vlookup" and "lookup" in Excel?
What is the main basic difference between VLOOKUP and LOOKUP in Excel?
I read the main point is that you can specify a range of columns in VLOOKUP and then pick one to return the value. However, it ...
4
votes
4
answers
1k
views
Spreadsheet forumula questions to pick a random DVD
I have a list of my (279) DVD���s in an OpenOffice Spreadsheet (version 3.3.0) on my Windows 7 laptop and I want to create a formula to randomly pick a film to watch (for occasions when I can’t make up ...
4
votes
1
answer
7k
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 C1 ...
4
votes
1
answer
7k
views
In Excel, doing a "Freeze Panes" on the right-most column instead of the left-most?
My understanding of Excel's "Freeze Panes" feature is that you can only freeze the left-most column(s), or top-most row(s).
However, I have a table where a very important column needs to be located ...
4
votes
4
answers
6k
views
Turn Excel spreadsheet into a formula [closed]
I have an Excel spreadsheet that has a complex computation that is not trivial to turn into a macro or a single-cell formula. The spreadsheet has about 10 different inputs (values a human enters in ...