All Questions
35
questions
0
votes
1
answer
42
views
Nested If statement. working in two sheets
its been a long time since I used Excell so my thinking may be complety wrong, but this wont work. Am I using the wrong Syntax for LibreOffice Calc
=IF(M3>$'Shipping Profile'.$A$3,then $'Shipping ...
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
-1
votes
1
answer
367
views
Find latest non-blank value no later than given date - and quickly
I'm really struggling to find a solution to this deceptively simple problem. I've even enlisted ChatGPT's help, but none of its solutions have worked. Maybe you can prove you're smarter than a machine ...
- 31
1
vote
1
answer
248
views
How to apply a calculation on a table column with a range of value per cell
I have a large table where in the first column for some row rather than just a single value there is a range e.g.
I want to apply a calculation on all the data elements, but since in the first column ...
- 225
0
votes
1
answer
1k
views
How to combine the values from multiple rows in a column (separated by commas) where there are duplicates in another column
I have a large dataset where I want to combine the values of multiple rows in one column where there are duplicates in another column. See my Example below:
CURRENT DATA
Product Type
Email
Product 1
...
- 135
1
vote
1
answer
716
views
Neater way to ignore errors in LibreOffice Calc?
Let's consider any (potentially lengthy) function that can produce an error. For discussion's sake, I'll use =1/0. I can ignore the resulting error by using the following formula:
=IF(ISERROR(1/0),&...
- 21
0
votes
1
answer
468
views
LibreOffice Calc: Exclude rows from INDEX() reference
Working on Sheet2, I can use the following formula to look up the "Sheet2.B1"th match in Sheet1, column B corresponding to Sheet1, column C:
{=INDEX($Sheet1.$B$1:$B$9,SMALL(IF($Sheet1.$C$1:$...
- 21
0
votes
1
answer
53
views
Spreadsheet: How can I merge every nth line with a previous line?
I have a spreadsheet where each cell contains multiple key value pairs. Every value is on a new line. The content of every cell is the following: cell content I need to somehow merge the every second ...
- 1
0
votes
1
answer
224
views
Conditional Format of cell based on the format of another cell
Referring to screen shot, I want to programmtically format a cell like so:
If cell B4 and cell F4 are both red text, then format cell J4 to be red text.
(and similarly for all other cells).
I see ...
- 314
0
votes
0
answers
30
views
Get addresses for all duplicates in a spreadsheet
I am trying to analyze output of performance benchmark. My spreadseet consists of multiple Linux top outputs spawned every 0,5 s for about a minute. I need to identify keywords that will help me ...
0
votes
1
answer
424
views
Lowercase only the leftmost character of a string in Excel
I have 2 columns containing strings. In the third column, I am trying to create a camelCase version of the strings, but I am stuck on how to accomplish the task. It has the following formula:
=...
- 211
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
2
answers
4k
views
Excel: how to count and group rows
I have an Excel file with Column A and Column B. Both have people names (Elizabeth, John, Michael and Robert).
I need to count each name of Column A and count each name of Column B. And then I need ...
- 99
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
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
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
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
1
answer
999
views
Find a Certain Cell based on other Cells in Excel/Calc
I have a spreadsheet:
Beans B-kg Chips C-kg
1.4oz/12 0.47544 6.5oz/20 3.679
1.48oz/12 0.502608 7oz/12 2.3772
1.86oz/12 0.631656 8oz/20 4.528
and a ...
- 35
1
vote
1
answer
2k
views
Calc - How to combine rows from multiple sheets into 1 main sheet
I have several statements separating my various bank accounts in Calc, they all use the same columns and have a Date column which I sort them by.
I want to create a 'Overview' sheet which contains ...
- 141
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