Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenSun, 21 Apr 2019 00:00:22 +0200Change all values in a column based on headinghttps://ask.libreoffice.org/en/question/191099/change-all-values-in-a-column-based-on-heading/I have a lot of spreadsheets I need to update, hoping there is a way to automate it.
What i need to do is if a column has one of several headings, i need to change all values in that column to a different number. For instance, in all my spreadsheets, all the columns where the first row heading is abc, I need to set the value of the rows under that to 0. if the heading is def, i need to set the value to 34, etc.
Also, there are quite a few headings that will need their values set to 0, if that makes a solution any simpler. Thanks for any assistance you can provide. odtxahvmSun, 21 Apr 2019 00:00:22 +0200https://ask.libreoffice.org/en/question/191099/I can't enter RE in the formula window.https://ask.libreoffice.org/en/question/189848/i-cant-enter-re-in-the-formula-window/When I type RE in the formula window, RE changes to R which is a differnt shape of R. So, I can't make formulas like x_RE or just RE. How can I enter those formulas? My LibreOffice version is 6.1.4.CGYOOWed, 10 Apr 2019 08:07:03 +0200https://ask.libreoffice.org/en/question/189848/Removing STYLE() breaks the formulahttps://ask.libreoffice.org/en/question/189608/removing-style-breaks-the-formula/Hi all,
some times ago I received a precious help from this community regarding the use of STYLE(). Well, now Im facing some problems when sheets is read on dropbox or with other suites so I decided to remove the STYLE() option.
This works for me like a violin :)
**=IF(C30="","",IF(COUNTIF(C6:C26,"MW|SA|PA|MA|PB|MB")=C30,"0"&T(STYLE("Good")),COUNTIF(C6:C26,"MW|SA|PA|MA|PB|MB")-C30&T(STYLE("Bad"))))**
Then I modified it this way
**=IF(C30="","",IF(COUNTIF(C6:C24,"MW|SA|PA|MA|PB|MB")=C30,"",COUNTIF(C6:C24,"MW|SA|PA|MA|PB|MB")-**C30**))**
Now the cell shows the value in C30, (the one marked italic). I mean, if I change the reference (eg C29) in the first COUNTIF still see the value of C30 in the second COUNTIF.
Which brings to my problem: I always see the value of the second C30.
Any suggestion?
Thank you in advancepaparucinoMon, 08 Apr 2019 16:18:04 +0200https://ask.libreoffice.org/en/question/189608/how to copy formula from calc to excelhttps://ask.libreoffice.org/en/question/187610/how-to-copy-formula-from-calc-to-excel/ Is any way copy the formula from calc to excelseichingWed, 20 Mar 2019 14:05:51 +0100https://ask.libreoffice.org/en/question/187610/Index Aggregate formula. Need expert help.https://ask.libreoffice.org/en/question/187518/index-aggregate-formula-need-expert-help/I hope someone can help me out with this..
The formula (listed below)works great in Excel but in Calc I get $Div/0! Errors. I think the problem is with the "aggregate" portion.
Here is the formula:
=IF(ROWS($AH$4:AH4)<=$AH$3,INDEX($S$4:$S$16,AGGREGATE(15,3,($R$4:$R$16=$AI$3)/($R$4:$R$16=$AI$3)*(ROW($R$4:$R$16)-ROW($R$3)),ROWS($AH$4:AH4))),"")
A shortened version of the spreadsheet is attached for download.
The picture illustrates what I'm trying to accomplish. I need to be able to change the value in AI3 (currently "3") and have the list update accordingly. Open in Excel and it works great. Open in Calc and I get errors. I need this for work and I only have Calc available there. Any help is very much appreciated!
[C:\fakepath\PARTFINDER.ods](/upfiles/15530300075189608.ods)
![image description](/upfiles/15530274481726606.jpg)kevingrg77Tue, 19 Mar 2019 21:19:04 +0100https://ask.libreoffice.org/en/question/187518/IF(formula populated currency formatted cell is greater than zero) always returns truehttps://ask.libreoffice.org/en/question/187083/ifformula-populated-currency-formatted-cell-is-greater-than-zero-always-returns-true/I have a calc sheet that maintains a loan amortization/payment history. Each time I make a payment I enter the payment amount and the calc sheet shows me the payment breakdown into principle, interest, escrow, etc... All cells are `Currency` formatted. The column definitions are as follows:
`D`: Escrow Amount
`F`: Principle Balance
`G`: Payment Amount
`H`: Interest Amount
Cell `F17` is populated with this formula: `=F16+H17-G17+D17`. This just means, the current Principle Balance equals the previous principle balance plus the current interest charge plus the current escrow charge minus the current payment amount.
It is likely that the escrow amount in `D` will be a fixed amount for a year until it is recalculated based on current tax rates. So rather than typing the escrow amount in each row (for each payment) I would like to convert `D` into a formula which basically says, if there was any balance left on the loan after the previous payment, then just copy the previous escrow amount into this months payment. This way, I only have to enter an escrow amount each time that value changes, once a year. The rest of the time the sheet auto populates it with the previous value. But, once the loan is paid in full, and there is no current balance to be paid, then the escrow amount should show 0 (zero) because there is no payment to be made. To accomplish this I am using this very simple formula:
`D18`: `=IF(F17 > 0,D17,0)`
`D19`: `=IF(F18 > 0,D18,0)`
...
`D[n]`: `=IF(F[n-1] > 0,D[n-1],0)`
This just means the current escrow amount is the same as the previous escrow amount if there is any principle remaining to be paid. If there is no principle left to be paid then the escrow amount is 0 (zero).
At least, this is what I think it SHOULD mean. But the formula is not working, meaning the boolean expression checking if the principle remaining is greater than zero always returns true, even when there is no principle balance remaining.
For example:
`D314`=`$188.56` `F314`=`$520.96`
`D315`=`$188.56` `F315`=`$0.00`
`D316`=`$188.56` `F316`=`$0.00`
I expect `D316`=`$0.00`, but actually it is `$188.56`
I don't understand what I am doing wrong. I know my logic is correct, but there is probably a problem with data types in the formula. Is there any reason an `IF` statement would always return true for a formula populated, `Currency` formatted, cell when checking if the value is greater than 0 (zero)?
What do I need to change to get this to work?axiopistySat, 16 Mar 2019 18:27:04 +0100https://ask.libreoffice.org/en/question/187083/Calc is unable to resolve dependent formulashttps://ask.libreoffice.org/en/question/185181/calc-is-unable-to-resolve-dependent-formulas/If have a spreadsheet using several quite complicated formulas "in a row", which also depends on each other. Recalculation triggered by changing the content of some (specific) cell results in a `#VALUE!` error in some cells.
Manually recalculation (using `ctrl`+`shift`+`f9`) fixes this.
As far as I understand, there is no circular dependency, but I can be wrong.
Am I doing something wrong? If yes, is there a way to fix the calculations?
If no, is this a bug?
I stripped-down the spreadsheet to some ["minimal" example attached here](/upfiles/15519012626643016.ods).
The original spreadsheet does make more sense, of course, but the core is still there:
The idea is to calculate start and end of some work package based on dependencies on other work packages. A work package itself can be structured, in which case the start is the earlist start of all sub packages, whereas the end is the latest end of all sub packages.
* Data can be entered in the cells with the light blue background: some identifier (name), some dependency, and some duration.
* Subpackages are denoted by a dot, i.e. `test1.a` means sub package `a` of package `test1`.
The obvious problem seems to be, that the end date located in row `G` is calculated using end dates of all sub packages (a `MAXIFS(G:G)` formular). For example in the attached spreadsheet end date of `test3.b` (cell `G18`) is calculated using data from cells `G3:G5`.
Imho there should be no (or at least *no unsolvable*) circular reference, since the conditions in the used `MAXIFS()` prevent that the result of `G18` is used at all. Nevetheless this seems to be the issue (i.e. causes the `#VALUE!` error). If I decouple the `MAXIFS()` ranges, i.e. handle row 3 to 10 by its own as well as row 11 to 20, it works like expected.
How to reproduce the issue in the attached example spreadsheet:
1. Delete content of cell `D8` (marked yellow) with key `Delete`.
2. Renter the very same value `test1` again.
Environment:
* Version: 6.1.5.2
* Build ID: 1:6.1.5-1
* CPU threads: 4; OS: Linux 4.19; UI render: default; VCL: x11;
* Locale: de-DE (en_US.utf8); Calc: group threaded
In the original spreadsheet I sometimes need to trigger the recalculation more than once to get every cell calculated as expected. Sometimes *LibreCalc* even crashes if I trigger recalculation too often. The last seems to be a bug for sure. Nevertheless I want to understand what I am doing before reporting this as a bug.doakSat, 02 Mar 2019 16:30:31 +0100https://ask.libreoffice.org/en/question/185181/Need help with Fuctions or a formula, don't know which [solved]https://ask.libreoffice.org/en/question/185627/need-help-with-fuctions-or-a-formula-dont-know-which-solved/I am trying to do a Function or formula or something, not even sure what I need to be doing or how to do it. It is for some hypothetical situations. What I am wanting to do is to have a column of random numbers, then 2 columns of numbers in percentage based off of the random number in the cell on that same row. So say Column A for the list of random numbers, Column B and Column C for the profits by percentage. The hypothetical numbers need to look real for the situation though. Like this :
random numbers deal profit trade profit
0.5924370951212 59.24% 592.44%
0.986644999118732 98.66% 986.64%
0.69899366207306 69.90% 698.99%
0.29986947345673 29.99% 299.87%
0.175831086796236 17.58% 175.83%
As you can see, some of the numbers in the 2nd column would be OK for a business trade or a stock trade. All of the numbers in column 2 would even be fairly realistic for a stock trade if you got very lucky on some trades. The numbers in column 3 would not be realistic for business transactions (unless you were a bank maybe? ) but some of them might look realistic for a stock trade, those trades like google or apple when you catch the stock way down or got in on the ground floor and held on.
I can go through the whole spreadsheet doing formulas one at a time and make it look realistic, but it is very tedious if you have like 100 or more random numbers you want to make realistic. So, I tried to do a formula in the first cell of Column B and Column C, and then drag & fill below so that they would process the numbers on each row of Column A. I tried nested IF Functions, even tried nested IF & AND Functions, trying to get results that would look realistic as hypothetical numbers.
But I couldn't make it work. The only way I could think of to do it was to have a separate IF Function for each range : like if it is .01 to .1 do this, if it is .11 to .2 do this, and so on up to if it is .91 to .99 do this. I could get it to work using an IF Function for A1<=.3, then like nested IF for A1>.3, etc. But I don't know how to specify from .11 to .2 in a Function. I tried like if(A1=.11:.2,(A1*1),0) but that did not work, got an error code.
Does anyone know how I might set this thing up so that I can use nested IF Functions and specify a range like .11 to .2? I was thinking nested IF's like (A1>=.1 do this, A1>=.2, A1>=.3, etc) would not work because if the number was like .3 then it would meet the requirements of all 3 if statements.
If anyone can figure out what I'm trying to say and knows a way to do this setup I wold appreciate the help. Thanks
Edit: My apologies for posting this question over on the OpenOffice forum as well, I should have given people more time to respond here. Quite embarrassing. I tried the method suggested in the comment by Mike Kaganski, I did not get any errors but the answer came out 0.00% -- so I must be doing something wrong.SteveH_66Wed, 06 Mar 2019 03:55:39 +0100https://ask.libreoffice.org/en/question/185627/calc autocalculate fundamentally broken? need help how to handlehttps://ask.libreoffice.org/en/question/185684/calc-autocalculate-fundamentally-broken-need-help-how-to-handle/
hi @all,
i'm new to 'online bug hunting', pls. be so kind and give some help,
one thing first, i like libreoffice, especially calc, imho it's much better than commercial competitors, i had massive problem with ex_el and i know why i changed, calc gave so much to me and my life, i ow it gratitude and respect!
>>> i criticise only! to get faults out and keep it's standard high.
imho calc autocalculate has one or plenty massive bugs in it, which exclude some cells from being recalculated after changes on them, on copies of them, in ranges they point to, and similar.
reports about this show up at least since 2013!, and since version 4.x, plenty of them mentioning 'obstructive', 'randomly', 'hard to reproduce'.
>>> i know!!! that working on such bugs is very!!! hard and difficult
but it's neccessary, reliable autocalculation is one of the fundamental functions of a spreadsheet,
'F9' - 'recalculate' doesn't help (in most cases), acc. to what i'd read while 'autocalculate' is on F9 recalculates only! intentionally changing cells with functions like 'random' and 'now' and their dependents, thus 'normal' cells and functions are not touched and not recalculated.
(irritating behaviour for users who use their private silly understanding of the word 'recalculate', difficult to understand without rtfm, but ok, it's written somewhere)
(it's not read by plenty persons, thus hints like 'Hast Du schon mal im Menü Daten>Berechnen>Neu berechnen (F9) angestoßen, ...' fill the scene)
'strg-shift-F9' - 'hard recalc', does! correct wrong results in most cases (my limited lifetime blocks me from checking 'all'), it is suggested as 'help' to irritated users ... imho that's 'eyewash':
- acc. to it's 'definition' in the online wiki 'autocalculate' should! recalculate every cell after any change in the sheet.
- there is no help to users which cells will fail and when they have to do manual recalculation,
- AND!!! ... strg-shift-F9 corrects the actually shown results in mostly when you 'fire' it, BUT LEAVES THE AFFECTED CELLS EXCLUDED FROM AUTOCALCULATE!!! thus you have to fire it again, and again, and again ...
- how shall a user produce reliable results with a spreadsheet where most of the cells are autocalculated while sometimes some of them fail???
i'm adressing this problem for some days now, and it starts that i get hints like 'you already filed bugs about it', 'that's not the same bug', 'why do you repeat known bugs?' 'use hard recalc', 'this bug is fixed', 'may be open-gl related', and so on, while it's evident to me that the fundamental weaknesses is still present,
>>> everytime i play around and look for errors like these i find new ones, not really new, but other situations where errors show up.
my two cents:
1. somebody introduced something like excluding some cells from autocalculation when they are not subject to changes, may be his intention was 'performance',
2. he strayed in decision about how to decide which cells may be excluded, especially when 'groups' of 'shared formulas' are in the game,
third cent:
- since then plenty developers plastered single symptoms of that weakness, leaving the fundamental bug untouched,
it's just my idea about the situation, i may be fundamentally wrong ...
i'm not! wrong in analysis that there is! at least one fundamental bug producing errors, that it is! still present in ver. 6.3.0.0.alpha0, and that it should be obliterated!
>>> so what shall i do???
file a new bug for every 'new' misbehaviour? e.g. there was a fault for '=SUM(of a range)', hen a new one for there is a fault for '=PRODUCT(of a range)' too?
file a new bug for every new place in a sheet i find wrong behaviour? e.g. there is a bug report for moving H7:G8 failing in one sheet, i shall write a new one for moving B1 to B2 produces a similar error in another sheet?
reopen bug reports where i find the error still occurring and get them 're-closed' implying i'm incapable to understand the subject that's written as headline of the bug report?
i think i'm able to understand 'Cells not recalculated after moving data within ranges pointed to by a shared formula group'
write a makro to test sheets and produce bug reports automatically?
no - that can't be the solution,
but i hope there could be a solution, how can i / can we find someone with the skills, time and mood to address this problem in detail?
imho it's urgent, if the bug is left unfixed i have to change to another software and recommend the same to others :-(
any help appreciated,
reg.
newbie-02
P.S. for those who need a detailed test case, use search or google for 'calc', 'autocalculate', 'recalculate', 'wrong', 'fail', 'error', 'excluded' and similar keywords, you'll find enough of themnewbie-02Wed, 06 Mar 2019 10:53:39 +0100https://ask.libreoffice.org/en/question/185684/[SOLVED]: (Python openpyxl does not support R1C1 syntax) Calc turns formula into lowercase, formula doesn't workhttps://ask.libreoffice.org/en/question/185483/solved-python-openpyxl-does-not-support-r1c1-syntax-calc-turns-formula-into-lowercase-formula-doesnt-work/I use Python to populate .xlsx cells with a formula '=RC[-3]+RC[-2]'
(Formula syntax: Excel R1C1)
When I open the populated file, the formula in cells is in lowercase '=rc[-3]+rc[-2]' and doesn't work (Err:509 instead of the sum).
Nothing in AutoCorrect options helps.
Why is it happening and how to fix it?
Thanks.librenewbieTue, 05 Mar 2019 02:56:22 +0100https://ask.libreoffice.org/en/question/185483/either or formula [solved]https://ask.libreoffice.org/en/question/185329/either-or-formula-solved/I have a spreadsheet with a page called Calculator. In cell C16 there will be a number between 1 and 60 inclusive.
In another sheet in the same spreadsheet I want to check whether the number in cell C16 of Calculator is below 16 or over 46. If it is return Y, if it is not return N.
Having a problem with thisbriansmithSun, 03 Mar 2019 21:53:51 +0100https://ask.libreoffice.org/en/question/185329/unprecise - switching - evaluation and results when formatting a cell as texthttps://ask.libreoffice.org/en/question/185398/unprecise-switching-evaluation-and-results-when-formatting-a-cell-as-text/ hi all,
imho a spreadsheet should be clear, precise and produce unique results,
(unless you work with random functions intentionally)
calc doesn't, whenever you change the format of a cell with a formula from number to text and vice-versa it produces irritating results,
i'd like to see your comments before filing that as a bug.
simple sample:
enter "1" in A1, all values without the quotation marks!
enter "=A1" in B1,
enter "=2*B1" in C1,
you'll see 1 | 1 | 2 in the row,
change format of B1 to text,
you'll see the result changed in only one little point, the "1" in B1 is now 'left aligned', the cell shows the result! of the calculation formatted as text,
imho the error starts here, a cell formatted as text shall show text, a programmer told me "no, it's your fault, you should explicitly change the content to text by adding a apostroph ('=A1)", thus she insists that showing "1" in B1 is correct,
if neccessary i could possibly live with that, i have difficulties to accept the next step,
in the above sheet enter the cell B1 and edit the content !!!without changing it!!!, e.g. insert a space and delete it while in edit mode,
after pressing enter the sheet will show changed content:
1 | =A1 | #VALUE!
while the formulas in the cells and their formatting are unchanged!!! cell B1 changes to show the text! of the formula instead of evaluating it,
that is a fault against the idea of the programmer, and in total it's certainly a fault having different results for the same input.
going back (formatting B2 as number and after that doing a 'no-changing-edit') will produce the same irritation, between the two steps the shown "=A1" in B1 is contradictionary to the formatting,
the behaviour is in plenty (all?) old versions of calc, it's still in ver. 6.3.0.0 alpha0,
imho it's based on 'when and what' is to be recalculated after changes, either you do! recalculation after changing the format, or you can omit it because the result won't change, but then it shouldn't change later on,
as one can see from the behaviour of C1 the evaluation is! important, as it does! affect the result of calculations ...
pls. tell what you think about it ...
regards
newbie-02newbie-02Mon, 04 Mar 2019 14:35:06 +0100https://ask.libreoffice.org/en/question/185398/SPLIT CELLS (HOURS)https://ask.libreoffice.org/en/question/183638/split-cells-hours/Hello i have spreadsheet and i have some data in column A that looks like this :
- 00:15
- 01:30
they are in time format
Now what i want is a formula so that i can split them into column B and C.
In B column there will be the 1st 2 digits before ":" and in C the 2 digits after them.
For example :
the 00:15 in A will become 00 in B and 15 in C
the 01:30 in A will become 01 in B and 30 in C
Any idea how i could do this ?
thanks in advancekik94Mon, 18 Feb 2019 08:06:26 +0100https://ask.libreoffice.org/en/question/183638/Find text after a specific character in Calchttps://ask.libreoffice.org/en/question/183510/find-text-after-a-specific-character-in-calc/ Hi there,
I am new here, and also new to Libreoffice. I have used Excel for many years, but have moved to Libreoffice recently.
I hope to find an answer to a problem i have extracting text in a cell, following a specific character. "@"
This character is part of a list of email addresses i am working on, so will not always be in a static position.
The Excel equivalent formula is: =MID(D3,SEARCH(@,D3)+1,255), where D3 is the cell reference containing the text.
This formula returns an error in Calc, despite me replacing commas with semicolons and placing @ within quotes "@".
e.g. johnnysmith@acmeremovals.com, or annedaly@acmeremovals.com. "@" is not at a fixed position in the text string.
I would like to extract acmeremovals.com only.
Thank you.Dave QuailSat, 16 Feb 2019 23:45:58 +0100https://ask.libreoffice.org/en/question/183510/#NA - ERROR handle in formulahttps://ask.libreoffice.org/en/question/183075/na-error-handle-in-formula/ Assume three columns **A**, **B** and **C**. Columns **A** and **B** can contain **text**, **numeric values** or **#NA**.
Column **C** should summarize the columns **A** and **B**
eg ***If*** `A2=123` and `B2=#NA` the result in `C2=123`
***or If*** `A2=#NA` and `B2=K2-123` the result in `C2=K2-123`
***or if*** `A2=#NA` and `B2=#NA`the result in `C2=` (empty cell)
***or if*** `A2=123` and `B2=K2-123` the result in `C2=123` (the same as cell A2)
Is it possible to handle **#NA** in a formula?AlbireoWed, 13 Feb 2019 15:22:23 +0100https://ask.libreoffice.org/en/question/183075/No change of address to a cell in calc, when new lines are inserted.https://ask.libreoffice.org/en/question/181398/no-change-of-address-to-a-cell-in-calc-when-new-lines-are-inserted/ Should try to explain my wish with an example (not easy) assume the following .:
Cell B3 contains the string "Info1"
Cell B4 contains the string "Info2"
Cell B5 Is an empty cell
Cell D3 contains the formula "=B3" - shows "Info1"
Cell D4 contains the formula "=B4" - shows "Info2"
Cell D5 contains the formula "=B5" - shows "0"
Don't know why "**0**" appears in cell **D5**, but I usually handle it with the formula `OM(B5 = ""; ""; B5)` (Swedish LO) I can handle the above. But..., assume that an empty cell is inserted between B3 and B4, the following results occur.
Cell B3 contains the string "Info1"
Cell B4 Is now an empty cell
Cell B5 contains the string "Info2"
Cell B6 Is an empty cell
Cell D3 contains the formula "=B3" - shows "Info1"
Cell D4 contains the formula "=B5" - shows "Info2"
Cell D5 contains the formula "=B6" - shows "0"
My wish is that the formulas in cells **D4** and **D5** should not change. I want still show the contents of cells **B4** and **B5** like this.
Cell D3 contains the formula "=B3" - shows "Info1"
Cell D4 contains the formula "=B4" - shows "0"
Cell D5 contains the formula "=B5" - shows "Info2"
**Is it possible?**
**How?**AlbireoFri, 01 Feb 2019 11:17:08 +0100https://ask.libreoffice.org/en/question/181398/Split cell value (numeric) and input half of the number into 2 cells.https://ask.libreoffice.org/en/question/179651/split-cell-value-numeric-and-input-half-of-the-number-into-2-cells/I have a calc sheet that I wrote for plan pricing along with unit pricing. Sometimes we do 2 for 1 specials. As is now, we have to calculate the pricing and punch it into 2 cells manually. Is there a way for Calc to look at a number in a cell and then have a drop down or button I can click that will look at the number, split it in half and then update 2 cells with that number? Example - Cell A3 has number $30. Can I do a drop down box and have it split A3 into $15 and B3 $15 by itself? ThanksMythEdgeWed, 16 Jan 2019 22:25:33 +0100https://ask.libreoffice.org/en/question/179651/Big troubles with spaces in formulashttps://ask.libreoffice.org/en/question/178556/big-troubles-with-spaces-in-formulas/ Hi, last versions of LibreOffice (6.1) have important bugs related to the integration of spaces in mathematical formulas, although theoretically those problems were solved at least three years ago.
[C:\fakepath\SpacesFormula.odt](/upfiles/15468879318152664.odt)
![image description](/upfiles/15468880164631193.jpg)JCarlosMon, 07 Jan 2019 20:08:15 +0100https://ask.libreoffice.org/en/question/178556/Copying only part of a formula from one column into the formula of another.https://ask.libreoffice.org/en/question/179314/copying-only-part-of-a-formula-from-one-column-into-the-formula-of-another/ I'll give the "Why" below, but my question is this: Is there a formula available that will fill in Column 1 with the last numerical digits of Column 2?
![Example](https://i.imgur.com/0G9I5ge.png)
**Why:** I am trying to get the total average of a list that contains items that are often duplicated. Column 2 currently shows the sum of the duplicates and I'm trying to give that sum a name using the final title given in its final sequence. (For example, B204-B207 all have the same basic name with a few minor differences, i.e. Libreoffice, LibreOffice, L1breOff1ce, etc.)
Thank you.tbakerMon, 14 Jan 2019 17:10:34 +0100https://ask.libreoffice.org/en/question/179314/How to calculate an additive tax in Calc?https://ask.libreoffice.org/en/question/178659/how-to-calculate-an-additive-tax-in-calc/Consider a tax (e.g income tax) which is applied in the format:
- 0-500€: 0%
- 500-1000€: 12%
- 1000-1500€: 28%
So if a person earns 500€, tax is 0, if they earn 1500, then the first 500€ have no tax, the next 500€ have 12%, and the final 500€ have 28%.
What is the best way in Calc to determine the earnings by providing the salary.mmalmeidaTue, 08 Jan 2019 20:20:36 +0100https://ask.libreoffice.org/en/question/178659/[SOLVED] Day() doesn't return the correct value. {Oops!
User Error}https://ask.libreoffice.org/en/question/176418/solved-day-doesnt-return-the-correct-value-oops-user-error/I'm currently working in Calc with dates in the 1300s and 1400s. The "Day()" function doesn't work on dates prior to 1582-10-15. For what I'm doing, all I need (RE this issue) is for Day() to return the "day of the month" part of the date; else, I need a reliable way to extract the "day of the month" component from the displayed date.
For my entertainment, I'm using a cell format that shows the date as "YYYY-MM-DD NN"; using the standard ISO8601 format doesn't help. I can change the format to show only the DD component; however, this is useless for my calculations, as the underlying information is unaffected.
An example of the problem involves calculating the years, months and days between two dates; in this case, I end up with a text value automatically displayed in either a "Y-MM-DD", a "YY-MM-DD" or a "YYY-MM-DD" format (with right alignment and a fixed-pitch font, the data presents nicely).
Simply subtracting the smaller date from the larger, and applying a date format, doesn't return the correct answer (a problem with even modern dates, but I'm not dealing with that right now).
I'm using LibreOffice Version: 5.1.6.2 and my OS is Linux Mint 18 "Sarah" with the "Cinnamon" GUI (I hope I expressed that properly).Havaneiss DeiSun, 16 Dec 2018 17:29:04 +0100https://ask.libreoffice.org/en/question/176418/Row duplication dependent on cell data?https://ask.libreoffice.org/en/question/178171/row-duplication-dependent-on-cell-data/I have a spreadsheet which contains user purchase data for a client. One of the fields provided is quantity. So I end up with something like this:
> FName | LName | ... | Quantity
Because nothing can ever be simple, they need an individual row for each item purchased. In other words, if the user purchased 5 of the item, they need the user data repeated 5 times in separate rows. I've been doing this by hand every month, but I've got to believe there's a way to do it programmatically. Sadly, I'm so far behind on my macro/formula programming I can't figure it out. Any suggestions?evertiroThu, 03 Jan 2019 20:16:41 +0100https://ask.libreoffice.org/en/question/178171/How do I replace a value that appears in multiple rows if one of the rows contains a certain value in a different column?https://ask.libreoffice.org/en/question/178168/how-do-i-replace-a-value-that-appears-in-multiple-rows-if-one-of-the-rows-contains-a-certain-value-in-a-different-column/Hi all, I've looked everywhere I know to and can't figure out how to do this. I'm also not entirely sure how to ask.
I am trying to search for a value in one cell and replace another cell based on that value. For example, I want to search in B2 and replace A2 if I find a certain value in B2. But I also want it to replace *anything in column A that matches A2*.
Below is an example of original data (on the left) and what I want it to end up as (on the right). In this example, it is searching column B for the characters "XYZ." It is then replacing the value in A2 with "NUM," since it found "XYZ" in B2, and replacing A1 with "NUM" as well since A1 matches A2. It's doing this for the others with "XYZ" as well. I did this manually for the example, but I want to do it with a formula, since I'm working in a sheet with nearly 100000 rows.
![image description](/upfiles/1546539631329536.png)
I know how to search for B2 and replace A2 based on that, but not how to have it replace both A2 and any matches to A2.
Is there a way to do this?lgreeneThu, 03 Jan 2019 19:30:50 +0100https://ask.libreoffice.org/en/question/178168/Creating a Macro that will run functions in certain columns.https://ask.libreoffice.org/en/question/177715/creating-a-macro-that-will-run-functions-in-certain-columns/ Hi Everyone!
I have 3 tabs "Instructions", "Raw_Data", and "Clean_Data". In the Clean_Data tab I have the following formulas written directly on the calc spreadsheet.
In Column A: =IF(ISNUMBER($Raw_Data.A1)=1,$Raw_Data.A1,"WRONG!")
In Column B: =IF(ISNUMBER($Raw_Data.B1)=1,$Raw_Data.B1,"WRONG!")
In Column C: =IF(ISNUMBER($Raw_Data.C1)=1,$Raw_Data.C1,"WRONG!")
In Column E: =$Raw_Data.B1
In Column F: =IF(ISNUMBER($Raw_Data.A1)=1,"I'm a number",IF(LEFT($Raw_Data.A1)="*",MID($Raw_Data.A1,2,4),MID($Raw_Data.A1,1,4)))
In Column G: =IF(F453="I'm a number"," ",IF(MID($Raw_Data.A1,6,4)="DUPL"," ",IF(MID($Raw_Data.A1,7,4)="DUPL"," ",IF(MID($Raw_Data.A1,6,1)="/",MID($Raw_Data.A1,7,4),MID($Raw_Data.A1,6,4)))))
In Column H: =IF(MID($Raw_Data.A1,6,4)="DUPL","DUPL",IF(MID($Raw_Data.A1,7,4)="DUPL","DUPL",IF(MID($Raw_Data.A1,11,4)="DUPL","DUPL",IF(MID($Raw_Data.A1,12,4)="DUPL","DUPL"," "))))
In Column I: =IF(LEFT($Raw_Data.A1)="*","*"," ")
Is there anyway I can enter these formulas into a macro and have them run through all the rows in the raw data sheet that contain text and the print the formula values on the clean data sheet? I'm trying to automate as much as possible and limit the chances for someone to be able to change the formulas by mistake.
Thanks for you help!mxc5425Sun, 30 Dec 2018 03:04:36 +0100https://ask.libreoffice.org/en/question/177715/Calc : How to edit formulae in formula bar directlyhttps://ask.libreoffice.org/en/question/177605/calc-how-to-edit-formulae-in-formula-bar-directly/Possible duplicate of [edit directly in cell](https://ask.libreoffice.org/en/question/86707/edit-directly-in-cell/).
To edit a cell formula, I move the cursor to the cell and then press F2. I can then edit the formula in the cell. I find this cumbersome for long / complex formulae and editing directly in the formula bar is a lot simpler for me.
My question : Is there a setting to force editing in the formula bar, rather than than in-cell, on F2 keypress?
(I know that I can click the formula wizard and edit long formulae there, but that's not what I'm after)
jan703Sat, 29 Dec 2018 01:40:13 +0100https://ask.libreoffice.org/en/question/177605/can i use sum formula in different language?https://ask.libreoffice.org/en/question/177382/can-i-use-sum-formula-in-different-language/I want to sum numbers which is in Bangla
but the sum formula isn't working with the Bangla numbers.
[C:\fakepath\test.ods](/upfiles/1545924318234430.ods)
made by m.excel in android - [C:\fakepath\Book (2).xlsx](/upfiles/15459827099231342.xlsx)
after opening it on libre office it sort of changed the font
so i converted it into ods and fixed font here - [C:\fakepath\Book (2) (copy 1).ods](/upfiles/15459827656871818.ods)redianThu, 27 Dec 2018 11:06:49 +0100https://ask.libreoffice.org/en/question/177382/How do I sort so that formulae are adjustedhttps://ask.libreoffice.org/en/question/177220/how-do-i-sort-so-that-formulae-are-adjusted/ I am indexing a book entering items in order of appearance, and setting up subtractions to give a column with the lengths of each item. When I sort them into alphabetical order, the item lengths are altered.vulpiusMon, 24 Dec 2018 18:22:35 +0100https://ask.libreoffice.org/en/question/177220/I can't see formula outputhttps://ask.libreoffice.org/en/question/177101/i-cant-see-formula-output/ Hello,
I can't see the value of my calculation. I checked Tools > LibreOffice Calc > View > Formulas. But it didn't work.
Here is the picture:
![image description](/upfiles/15455031692158034.png)
![image description](/upfiles/15455031836463809.png)
Any help is appreciated.
Regards,
ordinary
ordinarySat, 22 Dec 2018 19:27:59 +0100https://ask.libreoffice.org/en/question/177101/Formula picks out individual digits not whole stringshttps://ask.libreoffice.org/en/question/174067/formula-picks-out-individual-digits-not-whole-strings/ Hello. I am using the latest version of LO in Windows 10. I am attaching a condensed version of my .ods file for reference. Columns A to F are my data, and the formulas are in columns H to N and O to U. The formulas in columns H to N refer to data in row 1 and the formulas in columns O to U refer to data in row 2. I want an 'X' to print in the formula columns if the number (1 to 49) corresponds to any data in the respective data rows. It sort of works, but it is picking out single digits from the data instead of the whole numbers, and wrongly marking an X for the single digits. For example, there is an X in cell H3 because cell E1 (23) contains a 3; and the formulas in columns O to U show an X for 2 and 3 because of the 21 and 38 in row 2. I have marked the offending cells in orange.[C:\fakepath\Sample for Reference.ods](/upfiles/1543334267848347.ods)Larry in WaterlooTue, 27 Nov 2018 23:14:20 +0100https://ask.libreoffice.org/en/question/174067/Count with either/orhttps://ask.libreoffice.org/en/question/173944/count-with-eitheror/ I'm counting entries from a separate spreadsheet, and have figured out how to do that, e.g., =COUNTIFS(Catalog.E4:E6000,"x",Catalog.G4:G6000,"x",Catalog.M4:M6000,"x").
However, in some instances, there are other entries in the required columns beside "x", and I want to include all entries. So, in the above example, in "Catalog.M4:M6000" I need to include not only "x", but also "D" and "T". How do I adjust the formula to include all those entries?downiepaulTue, 27 Nov 2018 01:31:37 +0100https://ask.libreoffice.org/en/question/173944/