Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenSat, 16 Feb 2019 23:45:58 +0100Find 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/[SOLVED] I keep getting #VALUE! [closed]https://ask.libreoffice.org/en/question/173806/solved-i-keep-getting-value-closed/I have researched this and tried many suggested solutions that I found but nothing seems to work.
I have a spreadsheet with 12 pages labeled Jan to Dec.
I am trying to keep a running total of an item each month (ie Year To Date). For each month there may or may not be a value entered in the item cell. If there has been no entry since the start of Jan I want the total cell to be blank until there is an entry.
The item "Entry" cell is N54 and the "Total" YTD cell is O54 in each of the 12 sheets.
I want to add the "Entry" cell from the current month to "Total" cell of the previous month.
I have tried many ways to accomplish this but my current method is as follows;
Since Jan is the start of the year there is no previous entry I used =IF(N54>0,N54,"")
For the rest of the year I have =IF (AND($Jan.O54="", N54=""),"",$Jan.O54+N54) with of course "Jan" being replaced with "Feb" and then "Mar" etc as the year proceeds.
My problem is if the previous month "Total" has no value I get #VALUE!.
I understand that "" is supposed to equate to "0" but it does not seem to be so. I have tried different formulas but nothing seems to work. I have also tried " " and {} but that did not work either. It seems if I try to add the current "Entry" cell to the previous "Total" cell that does not contain an entered value and is blank except for the formula the error occurs.
I hope my explanation of my problem is understandable and any suggestions that would get this to work would be appreciated. I am using Version: 6.1.2.1 (x64).Charlie GoodDogMon, 26 Nov 2018 00:26:39 +0100https://ask.libreoffice.org/en/question/173806/Conditional Formatting Assistancehttps://ask.libreoffice.org/en/question/173889/conditional-formatting-assistance/ Hello Everyone,
First of all, let me take the chance to thank each one of your for reading my post and assisting me with my dilemma.
I require assistance with a conditional formatting formula.
I want to parse each word or segment in my spreadsheet so that only vowels and semivowels (a, e, i, o, u, y, and w) are made bold.
For example, if my spreadsheet contains these words, I want them to show up in the spreadsheet automatically as such:
1. star -> st(**a**)r
2. hap-py -> h(**a**)p-p(**y**)
3. birth-day -> b(**i**)rth d(**ay**)
4. wel-come -> w(**e**)l-c(**o**)m(**e**)
5. in-for-ma-tion -> (**i**)n-f(**o**)r-m(**a**)-t(**io**)n
If someone could please assist me with this, I would be more than grateful.
Thanks again!VittorioMon, 26 Nov 2018 17:08:15 +0100https://ask.libreoffice.org/en/question/173889/I need to do a count based on multiple columns in a separate spreadsheet. Ideas?https://ask.libreoffice.org/en/question/173705/i-need-to-do-a-count-based-on-multiple-columns-in-a-separate-spreadsheet-ideas/ I am collecting statistics on one spreadsheet, based on data on another spreadsheet. The statistics will be the counts of items in multiple columns, for instance, if Column A has "x", and Column Z has "x", then what is the count. Sometimes, the counts is from three or more columns.downiepaulSun, 25 Nov 2018 01:15:42 +0100https://ask.libreoffice.org/en/question/173705/Libre Math: space + in= epsilon, how to stop it?https://ask.libreoffice.org/en/question/173120/libre-math-space-in-epsilon-how-to-stop-it/Every time I try to write something like {age in years}, it converts it to be age%epsilon years. How to change it, is it a bug? If it is a bug someone report it.agitatedmonkTue, 20 Nov 2018 12:40:38 +0100https://ask.libreoffice.org/en/question/173120/Fast way to count duplicates in 30000 rows (LibreOffice Calc)https://ask.libreoffice.org/en/question/170969/fast-way-to-count-duplicates-in-30000-rows-libreoffice-calc/ Actually, I already have a partial answer!!! Conditional formatting with "Cell value is" -> "duplicate" !!! This way a check is performed for each user's new entry in "real time".
I need to check if duplicate entries exist in 30000 rows of a column (any value, but not blanks!) . I would like to keep track of how many duplicates during the filling process.
Ok, conditional formatting is a very effective visual indication and fast anough for my needs, but as I am not able to perform a loop to check the color of the cells (found some people against this approach!! Would be so easy! ) I need to find an alternative way to count the duplicates (as a whole, no need to identify how many for each case!).
I tryed the formula:
=SUMPRODUCT((COUNTIF(F2:F30001;$F$2:$F$30001)>1))
It works, but it takes two minutes to finish!!!
If you want to replicate my case. My 30000 entries are formatted as: letter "A" and numbers between 100000 and 999999, e.g., A354125, A214547, etc. Copy as text the result of "=CONCATENATE("A";RANDBETWEEN(100000;999999))" to save time.
Thanks!
PS: Does anybody know the algorithm used to find the duplicates in conditional formatting (it is fast)?
A macro solution is not the best, but is acceptable! ;)CharlesDayanSat, 03 Nov 2018 16:58:50 +0100https://ask.libreoffice.org/en/question/170969/Holidays spreadsheet...Please help!https://ask.libreoffice.org/en/question/170668/holidays-spreadsheetplease-help/ Hi! I'm stuck! I'm trying to simplify my life but it doesn't look like I'm going in the right direction...that's why I need help!
![image description](/upfiles/15410228195964762.png)
I'm trying to work out, with a apparently very complex formula, holidays that our employees accrued and use based on their hourly wage.
OK so let's take MARCH:
C4 & C5 are employee's hours worked;
D5 are holidays accrued (%12.07 of C3);
E4 & E5 is the pay/h earned by the employee that month (E3 it's the average pay considering hours worked [=((C4*E4)+(C5*E5))/(C3)]);
F3 it's simply hours of holidays taken by the employee.
Now, in March this employee didn't take any holiday so those 19.9hours accrued will go into April (paid £8.39/h).
In April this employee took 25hours of holidays so I would need to pay 19.9hours at £8.39/h from March + 5.1hrs @ £9/h from April.
And so on...
Is there any way to this with formulas?
Please help!
David
Specy88Wed, 31 Oct 2018 22:54:24 +0100https://ask.libreoffice.org/en/question/170668/How to get my formulas to Automatically calculate and update my totals once I enter a new value in a cell block.https://ask.libreoffice.org/en/question/167359/how-to-get-my-formulas-to-automatically-calculate-and-update-my-totals-once-i-enter-a-new-value-in-a-cell-block/ I am using calc in LO , and have entered my data in spreadsheet along with formulas for totalling columns and rows. I used it and all works UNTIL I change data in one of the cells. Then it does not automatically update the total. I have to go to the cell where the total is, and renter the formula and then it will work. How do I get it to automatically update my sums? liverloverTue, 02 Oct 2018 05:21:31 +0200https://ask.libreoffice.org/en/question/167359/Formula to find a row that uniquely matches multiple criteria, including a regular expressionhttps://ask.libreoffice.org/en/question/166273/formula-to-find-a-row-that-uniquely-matches-multiple-criteria-including-a-regular-expression/[C:\fakepath\MatchCriteria.ods](/upfiles/15374671682812127.ods)
The following formula counts the number of rows that match certain criteria. **Is there a formula that will find the row that matches those same criteria when there is exactly one such row?**
=IF(LEN(A2),
COUNTIFS(
$Sheet2.$A:$Sheet2.$A, "=" & A2,
$Sheet2.$C:$Sheet2.$C, "=" & COUNTIF($A:$A,"=" & A2),
$Sheet2.$D:$Sheet2.$D, ">" & SUMIF($A:$A,"=" & A2,$B:$B) - 0.5,
$Sheet2.$D:$Sheet2.$D, "<" & SUMIF($A:$A,"=" & A2,$B:$B) + 0.5
)
,"")
----------
I'd also like to be able to replace references to A2 in the formula with a regular expression string, which I can do in the formula above because COUNTIFS, COUNTIF and SUMIF all accept regular expressions.
The rest of this post is context for the above question
----------------------------------------
I have two spreadsheets (one sheet in each) both with data relating to the same list of people. The data relating to each person can be used to find which person matches which between the two spreadsheets. Only of the spreadsheets has an ID for each person. I need to add the each person's ID to the spreadsheet that doesn't have them.
To do this I've written a macro that
1) Imports both spreadsheets into a new spreadsheet. The first sheet (Sheet1) in the new spreadsheet is the one without IDs, the second sheet (Sheet2) has the IDs
2) Adds a column to Sheet1 ("Match Count") with a formula for each person which counts the number of rows in Sheet2 that the matches the person's name and other criteria (See below)
3) Adds a column to Sheet1 ("ID") with a formula that shows the ID from Sheet2 when the "Match Count" value is 1.
In Sheet1 there are one or more rows for each person, grouped together. Sheet1 has an "Amounts" field which has a numeric value in each row.
Sheet2 has one row for each person, and has the columns "Number of Amounts", which corresponds to the number of rows for the corresponding person in Sheet1, and "Total Amount" which corresponds to the sum of the "Amounts" values for the same person in Sheet1. For the purposes of matching people between the two sheets "Total Amount" can be up to 0.5 more or less than the sum of the corresponding "Amount" values in Sheet1.
There can be different people with the identical names but they will have different "Amounts".
This is the "Match Count" formula (without the nested SUBSTITUTEs for readability)
A is "Name" in both sheets
Sheet2: B = "ID", C = "Number of Amounts", D = "Total Amount"
=IF(LEN(A2),
COUNTIFS(
$Sheet2.$A:$Sheet2.$A, "=" & A2,
$Sheet2.$C:$Sheet2.$C, "=" & COUNTIF($A:$A,"=" & A2),
$Sheet2.$D:$Sheet2.$D, ">" & SUMIF($A:$A,"=" & A2,$B:$B) - 0.5,
$Sheet2.$D:$Sheet2.$D, "<" & SUMIF($A:$A,"=" & A2,$B:$B) + 0.5
)
,"")
That works great, but I want is a formula that gets data from the row that uniquely matches the same criteria when the "Match Count" is 1. **It seems to me that if I can determine that one row uniquely matches certain criteria then I should be able to find that row with a similar formula, but I don't see it. Maybe I'm missing something very simple.**
The first formula that I came up with works in most cases, but does not work when there are more than one rows in Sheet2 with the same name as the person being matched from Sheet1, unless the first row with the matching name in the Sheet2 happens to match all of the criteria.
A is "Name" in both sheets
Sheet1: E = "Match Count" (above)
Sheet2: B = "ID", C = "Number of Amounts", D = "Total Amount"
=IF(OR(ISBLANK(A2), E2<>1), "", IF(AND(
COUNTIF($A:$A,"="&A2) = INDEX($Sheet2.$C:$Sheet2.$C,MATCH(A2, $Sheet2.$A:$Sheet2.$A, 0)),
SUMIF($A:$A,"="&A2, $B:$B) - 0.5 < INDEX($Sheet2.$D:$Sheet2.$D,MATCH(A2, $Sheet2.$A:$Sheet2.$A, 0)),
SUMIF($A:$A,"="&A2, $B:$B) + 0.5 > INDEX($Sheet2.$D:$Sheet2.$D,MATCH(A2, $Sheet2.$A:$Sheet2.$A, 0))
),
INDEX($Sheet2.$B:$Sheet2.$B,MATCH(A2,$Sheet2.$A:$Sheet2.$A, 0)),"Failed") )
The other formula I came up with works but is VERY slow and uses the "MATCH(1, (...) * (...) * (...), 0)" version of the MATCH function which does not seem to allow regular expressions...
A is "Name" in both sheets
Sheet1: E = "Match Count" (above)
Sheet2: B = "ID", C = "Number of Amounts", D = "Total Amount"
=IF( OR(ISBLANK(A2), E2<>1), "", IFERROR(
INDEX($Sheet2.$B:$Sheet2.$B,
MATCH(1,
($Sheet2.$A:$Sheet2.$A=A2) *
($Sheet2.$C:$Sheet2.$C=COUNTIF($A:$A,"="&A2)) *
($Sheet2.$D:$Sheet2.$D < SUMIF($A:$A,"="&A2, $B:$B) + 0.5) *
($Sheet2.$D:$Sheet2.$D > SUMIF($A:$A,"="&A2, $B:$B) - 0.5)
, 0)
)
, "Failed"))
To get around the speed issue by using the Fast formula for all persons that have a "Match Count" of 1, and for any that the formula doesn't work for I can then use the Slow formula instead.
However I also need to be able to use regular expressions. Some of the rows with a "Match Count" of 0 (less than 1%) come about because of minor differences between the way a name is represented, like several spaces in the name in one sheet where there is only one space in the other, or a comma in one that does not appear in the other (e.g. "John Smith, Jnr"). I can handle those variations relatively easily by using a regular expression based on the literal name string, e.g, "John[, ]+Smith[, ]+Jnr". I can't to do that when the formula I'm using doesn't take take regular expressions. I have to use macro code to find the row with all the matching criteria. That works, but I feel that a formula similar to the "Match Count" one would be more efficient.
So - how do I find row that uniquely matches multiple criteria, including a regular expression?
[C:\fakepath\MatchCriteria.ods](/upfiles/15374671682812127.ods)MossyThu, 20 Sep 2018 20:31:16 +0200https://ask.libreoffice.org/en/question/166273/SEARCH function with RegEx parameter returns #VALUE!https://ask.libreoffice.org/en/question/166399/search-function-with-regex-parameter-returns-value/Hi
I've enabled RegEx from Options window but I still get #VALUE! for return value.
I'm using following RegEx syntax: ^(face|twitter|google)
And the whole formula has following syntax: =SEARCH("^(face|twitter|google)";E3)
LibreOffice:
Version: 6.0.3.2
Build ID: 1:6.0.3-0ubuntu1
CPU threads: 8; OS: Linux 4.15; UI render: default; VCL: gtk3;
Locale: fi-FI (fi_FI.UTF-8); Calc: group threadedJAKESat, 22 Sep 2018 09:53:42 +0200https://ask.libreoffice.org/en/question/166399/Formatting formulas in cellshttps://ask.libreoffice.org/en/question/166172/formatting-formulas-in-cells/When writing longer formulas in a cell. It would help readability a lot if you can use multiple lines.
You can use CTRL-enter but the line breaks disappears when pressing enter. ValdemarWed, 19 Sep 2018 23:37:38 +0200https://ask.libreoffice.org/en/question/166172/telephone instead of bracketshttps://ask.libreoffice.org/en/question/166133/telephone-instead-of-brackets/ Hello,
I have a problem adding a mathematic formula to my text. When I open the formula-Editor, I see telephones at places where I expect to be brackets. You can see it in this picture:[C:\fakepath\Libre office Formula.jpg](/upfiles/15373689411845058.jpg)
How can I fix this?
I would be very thankful for any kind of help!Immi127Wed, 19 Sep 2018 16:57:52 +0200https://ask.libreoffice.org/en/question/166133/vlookup formulas fail when Calc opened in another languagehttps://ask.libreoffice.org/en/question/164993/vlookup-formulas-fail-when-calc-opened-in-another-language/ I have a spreadsheet that uses linked CSV files to pull data into a worksheet. Formulas on other worksheets then use VLOOKUP to use data from that worksheet. This works fine when the language is set to English. But when I start Libreoffice in German the vlookup seems to fail with "Fehler: Falscher Datentyp" (German for "Error: Wrong data type").
I'm using Ubuntu 18.04.
**Specific Example**
The attached spreadsheet and CSV file contain data on UK listed companies (all public domain data). The "UK Shares Fundamentals Downloaded Data" worksheet was linked to the CSV file of the same name using these steps:
Sheet->Insert Sheet from file..->(select CSV file)->Click OK on Text Import dialogue->Enable 'Link' on Insert Sheet dialogue
The 'Cur Share Price' in the FTSE350 worksheet uses vlookup to get the current share price from that "UK Shares Fundamentals Downloaded Data" worksheet. When the spreadsheet is opened in English everything works, when it's opened with Libreffice in German any formula that uses the value of a 'Cur Share Price' cell shows "Fehler: Falscher Datentyp" (German for "Error: Wrong data type")
**Example Spreadsheet File**
The Spresheet itself:
[C:\fakepath\UK Shares Fundamentals.ods](/upfiles/15362843958179454.ods)
THe CSV file (needs to be a in a folder called 'Data' in the same folder as the spreadsheet, MUST be renamed to .csv (renamed ods just so I could upload here):
[C:\fakepath\UK Shares Fundamentals Downloaded Data.ods](/upfiles/15362844892193634.ods)
**Screenshots**
In English all is fine:
[C:\fakepath\LO-in-English.jpeg](/upfiles/15362843015922134.jpeg)
In German it doesn't work:
[C:\fakepath\LO-in-German.jpeg](/upfiles/153628433131141.jpeg)
timFri, 07 Sep 2018 03:42:51 +0200https://ask.libreoffice.org/en/question/164993/What colors can be used in formulashttps://ask.libreoffice.org/en/question/164693/what-colors-can-be-used-in-formulas/ I think this ought to be a simple question, but it isn't. I've done dozens of web searches, and searched the forum, and can't find the answer.
I want to highlight numbers in certain columns based on positive or negative. That much is simple:
[BLUE]0;[RED]-0
in "Format Cells" makes positive numbers blue and negative numbers red. But the blue doesn't stand out as much as I need.
This also works:
[GREEN]0;[RED]-0
but the green is too light and is difficult to read. So I tried this:
[VIOLET]0;[RED]-0
This does **not** work, even though the color table that shows up when I click on the Font Color icon includes Violet.
I can't find a list anywhere that says what colors will work in this type of cell format. This ought to be somewhere in the documentation, but for reasons I can't even guess it appears to be deeply hidden. I can't find it anywhere in the documentation, nor in this forum.
There are many entries that talk about using embedded code to define colors, or how to change background colors, or other color-related topics: but nothing I can find gives the list of pre-defined colors that will work to format numbers in a cell.
Could someone please tell me what the list is, or where to find it, or how to get other colors to work?BartZLederman@gmail.comTue, 04 Sep 2018 13:00:27 +0200https://ask.libreoffice.org/en/question/164693/