All Questions
35
questions
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
11
votes
5
answers
64k
views
How to separate number by its decimal point in Excel/Calc?
I've got a number in a spreadsheet like so: 28.686279
When I open this sheet in either LibreOffice Calc or Microsoft Excel, is there a formula function that returns the "whole number", I.e. 28. Also, ...
- 6,131
10
votes
2
answers
759
views
Convert the multi-step sum of booleans into a single formula
I have a few sensor readings and I'm checking to see if the values are in the acceptable range.
To do this I use an IF to check if the values are > or < the calculated (average) value. The ...
- 203
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
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
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
1
answer
3k
views
LibreOffice/Excel: SUM an adjacent cell if the value in the conditional cell is equal to N (SUMIF(?))
Disclaimer: I have no idea what I'm doing.
In LibreOffice Calc (3.6.2.2), I'm trying to SUM all of the values in G:x (where range is G2:G529), where B:x is equal to a given number N.
The data set ...
- 229
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
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
2
votes
1
answer
6k
views
How to count how many rows contain particular values in a particular set of cells in libreoffice and excel
This Calc file : http://dropcanvas.com/521xc/107 have a sheet called "Etat" and a sheet called "Total".
In Etat.F20 I have a formula that counts how many rows in Total have the value "Bug" in the D ...
- 491
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
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
1
vote
3
answers
139
views
Convert an Excel table from one structure to the other
Original table
I have this table in Excel or LibreOffice.
Unit number
Type
Name
1
Object
Top
1
Object
Bottom
1
Object
Left
1
Object
Right
1
Object
Back
1
Object
Front
1
Property
Right-Fixed
1
...
- 270
1
vote
1
answer
1k
views
How do I apply the MATCH() function only to a filtered range? Or is there another way to do that?
I have a filtered list of values. Let's say I have the numbers from 0 to 5, and 2 is filtered out:
A (with auto-filter against "2")
0
1
3
4
5
Now I want to find out whether one of the ...
- 2,423