Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenMon, 13 May 2019 09:56:16 +0200Correct year for a Work Weekhttps://ask.libreoffice.org/en/question/193674/correct-year-for-a-work-week/There are 2 years for Work Week 52: 2018, and 2019.
My formula in column A which calculates the Work Week apparently fudges the year if the Work Week straddles the end of one year and the start of another.
![image description](/upfiles/15577338778138691.png)
How do I correct my formula so rows 63 and 64 correctly read 201852?
=IF(D2="","",IF((WEEKNUM((D2-3),21))>9,VALUE(YEAR(D2)&WEEKNUM((D2-3),21)),VALUE(YEAR(D2)&0&WEEKNUM((D2-3),21))))
----------
To answer @Opaque's comment question: Left to right are Columns A, B, C, & D.
- A=Work Week
- B=Day (of week)
- C=Not relevant (Multiple entries for same date)
- D=Date
D2 doesn't influence Row 63, Row 2 was the first use of the formula while Row 63 was the first to reveal a problem with the formula as written. D2 influences A2, just as D63 influences A63.err504Mon, 13 May 2019 09:56:16 +0200https://ask.libreoffice.org/en/question/193674/The formula works except in one cell?https://ask.libreoffice.org/en/question/193663/the-formula-works-except-in-one-cell/I have a formula to audit some data which calculates correctly except in one cell.
I created a simplified spreadsheet as an example. The purpose is to compare the data on the $DailyLog sheet (Light blue columns on left) against the data on the $WeeklyPaystub sheet (Light yellow columns in center). The audit is done in the light gray columns on the right.
Column G21 should match F21, and H21 should say "OK".
![image description](/upfiles/15577274688762847.png)
The formula for G21 is
=IF($D21="","",SUMPRODUCT(($A$2:$A$5000)=$D21,$B$2:$B$5000))
The formula for H21 is
=IF($D21="","",IF(SUM($F21-$G21)=0,"OK",SUM($F21-$G21)))
Everything else has been converted to number data.
![image description](/upfiles/15577278654391542.png)
I wanted to upload an attachment of the simplified sample spreadsheet, but the site told me the file was too large. In actuality, the site claims the maximum file size is 1,048,576 kb aka 1,024 mb. My sample spreadsheet is only 19.8 mb.
Here's the [attachment](http://ge.tt/2MbIT4w2) on a file sharing service. It will only be there 7 days before the service deletes it.
Why is cell G21 the only cell not adding up correctly?err504Mon, 13 May 2019 08:41:17 +0200https://ask.libreoffice.org/en/question/193663/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/Calc: find and replace possible in matrix-formulas?https://ask.libreoffice.org/en/question/134251/calc-find-and-replace-possible-in-matrix-formulas/As described in ['SheetName.'-bug](https://ask.libreoffice.org/en/question/72701/calc-formulas-automagically-extended-with-sheet-name/) LibreOffice Calc continuously makes own files illegible by inserting 'SheetName.' to every cell-reference.
As workaround to manually repair spreadsheets (at least temporary) a possibility to **use find and replace** also **for matrix-formulas** would be very helpful.
Does any possibility exist?milanTue, 10 Oct 2017 19:17:49 +0200https://ask.libreoffice.org/en/question/134251/How to collect the latest sale date.https://ask.libreoffice.org/en/question/85764/how-to-collect-the-latest-sale-date/I'm trying to collect the latest sale date from a list of data in four columns A, B, C and D for each fabric.
Col. A contains 3 different values (either Linen, Cotton or Silk)
Col. B contains 5 different colours (either blue, green, orange, red or yellow)
Col. C contains sizes but I don't need this at this present time so ignore
Col. D contains the date of the sale
Columns A-D are endless as sales are added so the list will run into 1000s
In columns G-J I am displaying the collected data from the long list of sales.
Col. G contains each fabric (linen, cotton, silk)
Col. H contains each colour (blue, green, orange, red, yellow)
Col. I contains total sales for each fabric/colour combination
Col. J contains latest sale date
E.G linen blue 8 latest sale, linen green 4 latest sale etc...
For Col. I the total sales are working fine. I'm using =SUMPRODUCT(A2:A5000="linen",B2:B5000="blue") at I2, =SUMPRODUCT(A2:A5000="linen",B2:B5000="green") at I3 etc,, However, for Col. J I'm having no luck displaying the most recent sale.
For J2 I would like to return the most recent sale date for linen blue, J3 linen green, J4 linen orange etcâ€¦
Any advice is most appreciative as I have very limited knowledge.
totally stumpedThu, 19 Jan 2017 21:14:15 +0100https://ask.libreoffice.org/en/question/85764/calc - CALCulate mathematical formulaehttps://ask.libreoffice.org/en/question/83258/calc-calculate-mathematical-formulae/ Say I have three parameters:
m (in cell B2)
lambda (in cell B7)
mu (in cell B8)
where I can easily calculate
rho = lambda/(m*mu) (in cell B10)
I now want to calculate
``p0 = 1/(1 + (m * rho)^m/(m!*(1-rho)) + sum[n=1 to m-1]( (m*rho)^n / n!) ) ``
How do I enter the sum?
``(1/(1+(B2 * (B7/(B2*B8)))^B2/(FACT(B2) * (1-(B7/(B2*B8)))) + ???))``
note: I'm generating these formulae in bash so I don't need to use named cells and can re-use the generated formulae by copying the column and changing the parameters for your convenience:
lambda="B7"
mu="B8"
m="B2"
rho="($lambda/($m*$mu))"
echo -e "rho =\n\t$rho"
p0="(1/(1+($m * $rho)^$m/(FACT($m) * (1-$rho)) + ???))"
echo -e "p0 =\n\t$p0"
DiesNutsTue, 06 Dec 2016 12:06:57 +0100https://ask.libreoffice.org/en/question/83258/Why isn't VLOOKUP working here?https://ask.libreoffice.org/en/question/45925/why-isnt-vlookup-working-here/Okay, I was playing around with numbers in a Calc sheet and one of the formulas started doing something bizarre. It's the same formula in A5, B5 and C5 (with different reference cells), but C5 isn't working. Note that if you change C1 to "11", none of A5:C5 will work. If you change C1 to "3", A5:B5 will not work but C5 will.
I'm not trying to do something complicated with those formulasâ€”just look up the number "1" in another column, then return the value of the cell right next to the number "1."
What gives??? Why the errors and why only sometimes??? (P.S.: I tried "LOOKUP" also with the same results.)
[C:\fakepath\modular math.ods](/upfiles/1423385645322707.ods)
(For those interested in what the heck all the other formulas are, I'm playing around with the extended Euclidean algorithm and modular arithmetic equations.)WildcardSun, 08 Feb 2015 09:56:49 +0100https://ask.libreoffice.org/en/question/45925/How do I refer to an entire column in a formula?https://ask.libreoffice.org/en/question/35427/how-do-i-refer-to-an-entire-column-in-a-formula/In Excel, you can say `=MAX(A:A)` and it will give the max of the entire column, which will update as numbers are added to the column.
You can also say things like x axis is `=Sheet1!A:A` and as you add items to that column the chart updates automatically.
How do I do the same things in LibreOffice Calc?endolithFri, 13 Jun 2014 17:49:39 +0200https://ask.libreoffice.org/en/question/35427/Calculate # of shared elements in cellshttps://ask.libreoffice.org/en/question/26190/calculate-of-shared-elements-in-cells/I am struggling to get a calc formula to do the following calculations:
A B D G H A
A B C 2 1
A D E 2 1
A F G 1 2
B H I 1 1
As in the example, I need for the formula to compare two cells, and tell me how many common elements they share -without me stating which elements are there in any of the two cells, although they are always letters from the alphabet, and any given letter is never repeated in the same cell-. Just for clarification, the example is comparing what would be B1 with A2, A3, A4 and A5, and C1 to A2, A3, A4 and A5 respectively.
Any ideas on how to achieve it? Thanks!
EnricGTorrentsSat, 16 Nov 2013 02:29:26 +0100https://ask.libreoffice.org/en/question/26190/