All Questions
Tagged with worksheet-function spreadsheet
223
questions
81
votes
9
answers
329k
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 ...
- 27.9k
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 ...
- 1,441
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.
...
- 203
9
votes
3
answers
51k
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 ...
- 217
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 ...
- 225
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 ...
- 83
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 ...
- 597
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(...
- 113
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 ...
- 51
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 ...
- 615
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 ...
- 221
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 ...
- 2,473
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 ...
- 2,540
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 ...
- 197
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 ...
4
votes
1
answer
3k
views
Numbers conditional formatting based on a different cell in the same row
I have a Numbers spreadsheet, with "quantity required" and "quantity in stock" columns, then each part is on a row. I'm trying to use conditional formatting to turn a cell yellow when # required = # ...
- 1,238
3
votes
3
answers
3k
views
Using VLOOKUP for multiple lookup values, then summing the outputs
Example:
I have a sheet listing the number of calories for various foods.
In another sheet I have a list of some of those foods to be used in a meal, and would like to calculate the total number of ...
- 31
3
votes
3
answers
9k
views
How to create an array of values in LibreOffice Calc?
I'm using LibreOffice 5.1.4.2 Calc and need to calculate the internal rate of return for a certain payment (say, in cell A1), a certain number of times (say, 100).
If I had the value repeated 100 ...
- 181
3
votes
1
answer
3k
views
Assigning rank in OpenOffice (or LibreOffice)
In OpenOffice (or LibreOffice), is there a way to rank rows?
For example, let's say you have a spreadsheet where cell A1=20, A2=30, and A3=10.
I want to B1 to get a value of 2, B2 to get a value of ...
- 8,477
3
votes
2
answers
5k
views
How do I make a random selection in a spreadsheet using more than one criteria
I have a spreadsheet created in OpenOffice (V3.3.0 in Windows 7 32 bit) that I use as a database of my DVD collection. After asking a question in SuperUser, I have it so it can pop up a random title ...
- 2,473
3
votes
1
answer
247
views
Filtering over merged downwards cells in MS Excel-2016
Note: This question has been asked back in 2010 on this site here. And yet, I would like to post it one more time, showing what I did and what didn't work. Besides, Excel has changed quite a bit since ...
- 131
3
votes
1
answer
74
views
libreoffice /open office calc - visualise formula
Let's say I have two alternatives in columns C,D like so:
row 6: sample size
row 7: sample mean
row 8: sample standard deviation
and then want to do a t-Test, which requires (amongst other things)...
- 557
3
votes
1
answer
782
views
Sum values in specific rows based on whether the row has a value that belongs to a range
Here's what I'm trying to do:
I have a table that has several metrics (example: revenue, cost, etc.) for about a hundred different accounts. I can't modify this sheet because it gets auto-updated by a ...
- 241
2
votes
4
answers
7k
views
How to calculate how much I've worked per day?
I'm having a problem when I try to calculate how much time I've worked per day by using an Excel formula. It returns valid results for when I've worked until 12:00 PM but if I passes it, values come ...
- 121
2
votes
3
answers
1k
views
How do you properly align average and standard deviation outputs in a spreadsheet?
I am not sure of the best way of formulating this question right now so I will use an example using random numbers. I start with values assigned to IDs, so that each ID can be n=1, n=2, ... etc
ID ...
- 23
2
votes
1
answer
968
views
How do I return a column value based on whether another column is within an array range - Libreoffice Calc
If I have a comparison array like so on sheet "A"...
Size
Min
Max
SM
0
99
MD
100
199
LG
200
999
...and on sheet "B" I Have...
Val
Size
50
??
250
??
150
??
...
...
How ...
- 277
2
votes
1
answer
1k
views
CONCATENATE cells in a column IF condition in other colum is met
Using Google sheets (or Excel if Google sheets can't do it). I'm trying to concatenate any cell in B column, if its corresponding A cell is "include". The columns are very long and therefore ...
- 25
2
votes
1
answer
206
views
Devising Excel Spreadsheet Formulas
I'm using Excel for the accounting purposes of a group venture, to keep track of our expenses, costs, payments, and balances.
Details of each expense are stored in columns A:J
Total costs and ...
- 393
2
votes
2
answers
106
views
What is { } used for in Microsoft Excel 2010?
I came across this function in one of my worksheets and wanted to know what the curly braces are used for.
Here is an example.
{=array(sheet1!a1,sheet2!a3,sheet2!a5,sheet2!a6)}
- 23
2
votes
2
answers
219
views
Creating an Invoice in Excel
I have only dabbled with Excel and not much in quite some time now.
I have an Excel Invoice laid out so Column A is a name of a job and each row in column A can be a different job.
Now column B ...
