All Questions
35
questions
1
vote
2
answers
387
views
VLOOKUP - Linking Documents
I am using Libreoffice calc and trying to link values on 2 separate documents
Doc 1 take the form
id parent ID Title Att1 Att2 Price Reg Price Stock
1001 0 ...
- 145
0
votes
1
answer
72
views
Combine column data of two sheets based on formula
In Libre Office Calc (or Excel) What formula to use (in sheet 3) in order to combine data of sheet 1 and 2 based on the ID column?
Get the last name and style from sheet 2 based on ID column
Sheet 1
...
1
vote
2
answers
364
views
Test whether the values in a row in two columns exist in any row in two other columns in Excel
I have 5 columns where I need to check whether the values of A and B of a row are found in the same sequence on any row in C and D, then output the result in E. The result will be true or false for ...
1
vote
0
answers
246
views
Dynamic timeline spreadsheet with selective stickiness?
I'm trying to create my first spreadsheet ever, but there's one function that I'm finding very difficult to find guidance for from the internet, just like the title of this post felt a bit clumsy to ...
- 11
0
votes
1
answer
73
views
How to apply a fomula multiple times at different intervals to entire column? OO Calc
I am using Open Office Calc on windows 8.1 Pro
I have the following data already sorted nicely.
A B C
--+-----------------------
1 | Bmw Gold £80
2 | Bmw Silver £60
3 | ...
- 15
0
votes
1
answer
123
views
lookup several values in a table and return the sum of those values
I'd like to lookup several values in a second table and add them up.
let me go into more detail
I have one table with numeric values and id's
id||amount
>----------<
3||12
34||8
16||40
...
- 115
2
votes
1
answer
1k
views
How to count the number of cells in a range containing a specific (sub)string with Excel/Calc?
Using Excel/Calc I have the following situation:
a range of cells containing values; assume it's A1:B10,
and a string I want to look for; for example, assume it is contained in C1.
Now what I want ...
- 1,996
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
14
votes
1
answer
23k
views
Switch Calc (or Excel) formulas to English language
I'm not using an American/English version of Calc/Excel so, every time I want to post here (or on other "web-places") or when I want to try formulas posted by others I need to "translate" them somehow ...
- 1,996
0
votes
1
answer
1k
views
Dynamic array/matrix generating within formula
The following formula:
=TRIM(A1:A20)
would make no sense; you typically should decide a column, write =TRIM(A1) on first cell and copy until =TRIM(A20) ... BUT ... suppose you want/need to use it ...
- 1,996
4
votes
1
answer
8k
views
LibreOffice Calc equivalent to Excel "Evaluate Formula" feature
Excel has an "Evaluate Formula" feature (it's located in the Excel Formulas ribbon among the Formula Auditing group, as shown in the picture below).
Is there an equivalent to that Excel feature in ...
- 1,996
0
votes
1
answer
861
views
How to copy tab-contained text from Excel without quotes?
I've noticed that a cell that contains a tab is copied with quotes embedded. I'm looking for copying such kind of a cell without the quotes.
Illustrative Example
I've 5 cells; A1, A2, A3, A4, and A5....
- 1,783
1
vote
3
answers
425
views
How to create the equivalent of CHOOSE using a list in a cell reference
Selecting a specific value from a comma-delimited list can be performed with the CHOOSE function. For example:
=CHOOSE(A1,"A","B","C","D")
where A1 contains the index value, and "A","B","C","D" is ...
- 27.6k
2
votes
1
answer
1k
views
How do I convert dates from Open Office format to MS Excel format
I am trying to save a file in Libre Office as an excel file but Excel sees the data displays it as a negative number using a series of ###### symbols.
I believe the problem is to do with the two ...
- 21
7
votes
4
answers
30k
views
How to do a "great circle" calculation in MS Excel or LibreOffice?
I got two columns with latitude and longitude values. I need to calculate the distance between the points in every row. I'm trying to use the haversine formula as seen here:
That's the so called "...
- 1,162