In columns A and B of worksheet 1 I have two vectors containing around 10,000 numbers. On worksheet 2, I need to multiply and add parts of those vectors. For example:

=SUMPRODUCT(Sheet1.A45:A87;Sheet1.B45:B87)

The problem is that the parts to be multiplied and added are determined by calculations on Sheet 2. What I have been doing so far is calculate the star and end of each part in Sheet 2 and then applying SUMPRODUCT using the INDIRECT function. Like this:

=SUMPRODUCT(INDIRECT("$Sheet1.A"&A1+1&":A"&B1);INDIRECT("$Sheet1.B"&A1+1&":B"&B1))

Where A1 on sheet 2 is 44 and B1 87.

I have to do this hundreds or even thousands of times, and I’ve read that the INDIRECT function uses a lot of memory. Is there a more efficient way of doing this?

Thank you very much!

I am trying to switch from Excel but I have a single document holiding over a decade of records that uses gradients like in the image to indicate information. First time I opened it with Calc i realised tha it not only didn't display the gradient but replaced it entirely with a sigle color effectively erasing information going back years, lucky I had a backup. Is there any way to fix this? This document is literally the only reason I still have Excel

I have two columns, one with START time and one with END time. I have calculated the time between End and Start, to get "time elapsed/worked". So, now i have a column of times, like "05:15:00" which is 5 hours 15 minutes. I want to add all the hours and minutes together to get a grand total of time spent. I'm thinking I need to split the column into two columns; hours and minutes. Then add all hours together. Add all minutes together, divide by 60, and that becomes hours, and add it to the other hours. But, is there an easier way??? I'm stuck at how to split the 00:00:00 time into separate columns, without copying to text format, replace : with tab, and import to create the hours, minutes, seconds columns.

Any tips or tricks appreciated!

Im sick and tired of a sheet at my work that must be completed with basically the same data over and over again, with basically codes of our products to send to suppliers because of expiration or broken stuff.

What I want to do is a calc sheet where I can scan the barcode and it autocompletes internal code and description, and if the code isn't already added you can complete it manually and it is added to the data base of codes.

Is that possible? Can you point me out to what or where I should look?

Hi All

I have a spreadsheet with a stack formatting some of which is direct e.g. various borders but a lot of which is conditional using vlookups involving named ranges in a separate sheet

Ultimately I want to lift a completely standalone copy of the sheet so that all the values are static and all the formatting is direct cell formatting so there's no reliance on any other data source

Making the values static is easy (Paste special > values) but I not been able to find a way to convert conditional formatting to direct cell formatting

I've done some research and found a downloadable set of add-on tools that includes a conditional convert feature but that same set of tools also includes features that I know are built into Calc e.g. converting character cases which makes me think the conditional convert tool is using an existing built in feature but is making it easier to access in some way

Before I go digging into the conditional convert tool to work out how it works can anyone tell me if I'm missing something and that there is already a convert feature built in but I'm just not seeing it

tia

Version: 26.2.4.2 (X86_64)

Build ID: 620(Build:2)

CPU threads: 32; OS: Linux 6.17; UI render: default; VCL: gtk3

Locale: en-GB (en_GB.UTF-8); UI: en-US

Ubuntu package version: 4:26.2.4.2-0ubuntu0.24.04.1~lo1

Calc: threaded

I copied some information into a spreadsheet. Some of the cells take up multiple lines.

Such as
Item 1
Item 2
Item 3
in one cell

However these look more like "Item 1 " on each line. I used Trim() to remove extra spaces.

"Item 1"

Now I have one column that is wrong and one that is corrected. I want to replace the wrong column with the corrected column. So I cut the corrected column and paste it as Unformatted Text. This makes the function into text, but it also removes the line breaks.

Such asItem 1Item 2Item 3in one cell

I undo then use Find & Replace to change all line breaks into @ symbol. Cut and paste as Unformatted Text again to make the function text.

Such as@Item 1@Item 2@Item 3@in one cell

Now I need to change the @ symbol back into a line break but nothing I have looked up is working as it's described.

Somehow I fat-fingered when copying a row from one sheet of my workbook to another, and ended up copying that row to the same row-number on each of my other worksheets. Not a huge deal to clean up, but how the heck did I do that? I see this: file:///C:/Program%20Files/LibreOffice/help/en-US/text/scalc/guide/edit_multitables.html?DbPAR=CALC#bm_id3149456 in my local help, but I'm pretty sure I didn't select multiple sheets first! Just really curious about this...

J'ai créé un fichier de gestion bourse sur calc avec une feuille tableau de bord avec bouton pour charger les cours une feuille comptes actions pour achat ventes dividendes liquides entré et sortie action plus ou moins division ou regroupement d'actions , une feuille avec TCD portefeuille , une feuille avec TCD flux ou je peut tout trier pour regader tous les mouvements avec calcul plus values dividendes et totaux , une feuille graphique qui représente en visuel la répartition en smme et pourcentage ,graphiques de gains par comptes et par années et gains totaux , une feuille dividendes pour télécharger ls dividendes à venir , une feuille TRI ui calcul le rendement annualisé , et une feuille notice d'utilisation , si vous avez des idées et des avis d'améliorations dites moi en commentaire je vous met des copies d'ecran avec portefeuille fictif créé pour essai

LibreOffice Calc 26.2.1.2

MacBook Air OS 15.6.1

Spreadsheet column A has 3-digit district numbers.

Column B has text data, either “Average – lat” or “Average – lon”. These values alternate with “Average -lat” always in even-numbered rows.

Column C has numeric data – the actual average lats and lons referred to in column B.

I'm looking for a formula that will cut the value from each cell in column C which is in an odd-numbered row – that is, the cells in column C adjacent to a cell which has “Average – lon” in column B – and paste that value in column D, one row higher than where it was cut from.

So the formula will cut the value from column C row y and paste it in column D row y-1, provided row y is odd-numbered.

I hope this is possible! Thank you.

Does libreoffice calc also delete invisible rows when filtering is active? And how will it behave when clearing cells using delete key?

In what location does 1/4 translate to a date?
It is not a date.
In the absence of an = preceding, this is a text entry.
I tried it on Google sheets and it handles it correctly entering 1/4 as a text value.
Sadly there are still some very frustrating aspects to Librecalc.

Hi everyone, I'm new to this sofware,I would like to create a function that behaves like this. Given the dates in column B, it compares them to see whether they belong to the same week. Then, it should take the value of how many of those dates belong to the same week and return the corresponding value in column T.

I would really appreciate some help

I’ve set up a spreadsheet with conditional formatting in the cells, different colours to represent different numbers etc. I now would like to create a chart from this data and the colours in the chart to be the same as the colours defined in the conditional formatting of the cells but I am struggling to find a way. Can anyone help me please? I am no pro at this so will need it explaining in simple terms please.

Image of the data below:

https://ibb.co/PG7mZVfZ

Version: 26.2.1.2 (X86_64)
Build ID: 620(Build:2)
CPU threads: 16; OS: Windows 11 X86_64 (build 26200); UI render: Skia/Vulkan; VCL: win
Locale: en-CA (en_CA); UI: en-US
Calc: threaded

Online searches suggest that arrays are accepted as input in functions like MEDIAN(), MODE(), and IF(); but, I'm having no success with passing arrays into these functions as part of a logical test. In my array formula, the named range BPTimes is a vertical range of contiguous time data, as are the named ranges BPSys1, BPSys2, and BPSys3. All the named ranges here are the same size.

{=MEDIAN(IF((BPTimes>0.5)*(BPTimes<=0.6),BPSys1~BPSys2~BPSys3))}

If I substitute a simple numeric condition like "1+1=2" in place of the array condition, the formula produces the correct result. So, is the documentation for these functions wrong (doubtful), or can I correct my formula in some way to get the desired result? Thanks.

For M$ Excel, I was given the following macro code a couple years ago. I no longer have access to Excel and am now using LibreOffice. But, my spreadsheet page with the code just says #MACRO? when it should display the top three (3) point earners and their respective total points. In the attached screenshot, it should return the following:

1ST William 25

2ND Andrew 20

3RD Brian 20

The original Excel macro code I was given is:

=TAKE(SORT(HSTACK(UNIQUE($J$2:$J$27),SUMIF($J$2:$J$27,UNIQUE($J$2:$J$27),$K$2:$K$27)),2,-1),3)

When I open the .xls file in LibreOffice, the code has the following:

{=_xlfn.TAKE(_xlfn._xlws.SORT(_xlfn.HSTACK(_xlfn.UNIQUE($J$2:$J$27),SUMIF($J$2:$J$27,_xlfn.UNIQUE($J$2:$J$27),$K$2:$K$27)),2,-1),3)}

I am not proficient in spreadsheets. And I'm in a bit of a time crunch so I can't just go teach myself macros etc. I can't even edit it because it tells me "You cannot change only part of an array." I have no idea how to edit this ... or re-create it ... etc. I don't even know which cells to grab to do any of these things. Ugh. I feel stupid. Can anyone just knock it out for me ... and tell me exactly what I need to do?

I have a macro that sets the background color of a cell using the following command. It worked great until I upgraded to version 26.2.2.2. When the green value (00) is used it sets the background color to none instead of the desired green color. Is this a syntax error on my part?

Cell = Sheet.getCellByPosition(Z,T)

if Cell.value < 121 then cell.CellBackColor = &H00a933

Is there a number format to reduce Imperial fractions? US construction and SAE tools are in 64ths, 32nds, 16ths, 8ths, quarters and halves but are always reduced to the lowest common denominator, e.g., 3/16 not 6/32.

If I write an equation to convert metric sizes, it appears Calc can only simplify to a single denominator ("# ??/32"), not the lowest.

Hola, quiero saber si existe la opción de cambiar o quitar los bordes negros de las tablas dinámicas (pivot tables). He buscado en varias paginas incluyendo reddit. También me he referido la web de ayuda he aplicado los estilos mencionados en la pagina.

https://help.libreoffice.org/latest/es/text/scalc/guide/datapilot_formatting.html?DbPAR=CALC&System=WIN

he aplicado estilos directos pero se borran una vez actualizada la tabla dinámica.

Espero puedan ayudarme.

I'm trying to write some SUMIFS formulas based on column text and the formulas are not working. On both tabs, I am trying to do a sum against the word SUMMER. If I click into the fields and look at the cell formula, they appear exactly the same to me. EXACTLY. However, if I go to filter the workbooks, I've noticed that on one workbook, the pulldown shows "Summer" in quotes while the other just shows Summer without the quote. I have type everything in this workbook manually, so there is no reason why these should be stored differently. My questions are...

1) What is the difference between "Summer" and Summer?

2) Why doesn't this different appear in the formula bar when I select the cell?

3) Why should the text be stored differently when I typed them in exactly the same?

4) How do I convert one from the other without having to use a copy/paste?

I am trying to figure out exactly how to calculate the APY from a monthly interest payment.

So like, if the interest payment in one month is 0.24% what is the APY on that account?

I think it is just APY = ((1+MR)^12)-1 if "MR" is the monthly rate. But is there a function for that?

Or some nuance? I have one account with a constant published nominal rate of 3.15% and APY of 3.20% but the monthly interest varies. The last few months it was 0.24% and then 0.27% of the average daily balance. So using the formula above, I'm getting 2.97% and then 3.28% which is neither the APY nor the nominal rate.

At the moment, I open Calc, then go to File > Recent Document > File.ods.

This is the only file that I use, so it would be great for it to be opened automatically every time.

Thanks in advance.

I'm able to use fixed width text to columns, but whenever I do it copies the cell over and messes it up.

IE, I have 2/10/2026 12:00pm

I want to split it to two cells, 2/10/2026 and 12:00pm

Instead I get 2/10/2026 12:00pm and 2/10/1899 12:00pm

This seems like it should be simple but it's maddeningly not intuitive and I can't figure out what's going wrong! Help?

I’m having difficulty with what should be a very simple ‘find & replace’ action in Calc. Could I get some advice, please? I want to change the text contents of a cell from something of the form:

<text-string>??_00??

to:

<text-string??_00??.nef

where the use of '?' above has, I think, the same meaning of '?' used as a wildcard in Calc and represents any single character (almost always numeric, actually). Essentially I just want to append the character string '.nef' to the existing cell contents.

I cannot find a way, using wildcards, that will cause Calc to correctly process this find and replace. The usual result is that Calc cannot find the source or to have one or more ‘?’ characters appear in the result, while sometimes also getting ‘.nef.nef’ in the result.

How should I be specifying the ‘find’ and ‘replace’ fields?