Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenWed, 02 Sep 2020 03:04:45 +0200- Use if ... then ... else in Calc formulahttps://ask.libreoffice.org/en/question/263685/use-if-then-else-in-calc-formula/I am working on a sheet that helps check my account activities.
![image description](/upfiles/15990082417031721.png)
How can I make Column "G7(NIP)" display 53.75 if value in Column "E7(Debit)" is greater than or equal to 50000; display 16.13 if less than 50000, or display "0" if empty?
Thanks in anticipation and I hope I am clear enough.........desiyaWed, 02 Sep 2020 03:04:45 +0200https://ask.libreoffice.org/en/question/263685/
- in calc how do i get the "else" in an IF statement to leave the cell blank when the initial condition is not met?https://ask.libreoffice.org/en/question/51709/in-calc-how-do-i-get-the-else-in-an-if-statement-to-leave-the-cell-blank-when-the-initial-condition-is-not-met/ Doing a simple spreadsheet of my weight loss progress. Plotting the weight vs. date. Future values are blank so that even though the plot range goes into the future, the plot stops with today. Now trying to calculate a 10 day moving average with the following IF statement. IF(cell# >0, sum(10 cell range)/10, ???) What do I put in as ??? in order to have the cell remain empty if there is no value in "cell#"? Leaving the ELSE value blank results in the cell having a zero value. If the 10 day moving average is plotted then everything after today will have a wrong value and the plot will go to zero after 10 days. Don't want anything to appear in plot after today.jerrysg7Tue, 09 Jun 2015 05:23:25 +0200https://ask.libreoffice.org/en/question/51709/
- How to Sum only if range totals greater than 3https://ask.libreoffice.org/en/question/37366/how-to-sum-only-if-range-totals-greater-than-3/hi, Im trying to create a field totalling how many "triple play" orders I've gotten throughout the day. So far Ive done some pretty decent formulas to keep track of my sales, but this particular one eludes me.
If the Cable, Tv, and Phone cells each have a 1 in them, then I'd like the Triple Play Totals cell to totall how many rows (accounts) have all three of those cells marked. If a row (account) doesnt have all three marked with 1's then the Triple Play Totals field doesnt add them in the total.
I hope this makes sense, any advice is appreciately very much.
netsharkTue, 22 Jul 2014 20:57:33 +0200https://ask.libreoffice.org/en/question/37366/
- IF function syntax helphttps://ask.libreoffice.org/en/question/34158/if-function-syntax-help/Hi,
I need help translating this bit of code into a IF function:
int time=1034;
int test=2000;
int count=0;
string conf="A change has happened";
if(time!=test) {
count++;
test=time;
cout << conf;
}
so basically what I would like to do is to change values of multiple cells depending on a result of the IF function.
Something like this: IF(NOT(A1=B1),*{C1=C1+1;A1=B1;D1="A change has happened"}*)
Thanks everybody for your help.Aljos@Mon, 19 May 2014 08:54:36 +0200https://ask.libreoffice.org/en/question/34158/
- Help using an IF statement - CALChttps://ask.libreoffice.org/en/question/31494/help-using-an-if-statement-calc/Here's my IF:
=IF(B3>0,SUM(D3*B3),"-")
Cell Data is:
B3 = 4
D3 = 14.99
show my IF should give me 59.96, instead it just says TRUE. What am I doing wrong here? I want the numeric value to be displayed, not whether the first part of my if is TRUE/FALSE
BeardyMikeWed, 19 Mar 2014 17:53:53 +0100https://ask.libreoffice.org/en/question/31494/
- IF not working in Calchttps://ask.libreoffice.org/en/question/30336/if-not-working-in-calc/Greetings:
I'm using v4.1.4.2 for Mac. I would upgrade, but the only mirror the downloader keeps sending me to is offline (a different issue).
The IF statement no longer seems to work in Calc. The built-in help tells me the syntax is:
IF(Test; ThenValue; OtherwiseValue)
And then gives the example:
=IF(A1>5;100;"too small")
My actual line of code is:
=IF(AC2<>0,$Y2,0)
I see it too. Those are supposed to be semicolons, not commas. Except I put semicolons in but the program automagically changes them to commas. I assume this is the reason for the failure.
What is shown is either "TRUE" or "FALSE", not the numeric value in Y2 or a zero.
This used to work, so not sure what's happening. Is this a recently introduced bug? Do I have a corrupted config? Do I have a corrupted install of LibreOffice? Something else? How do I get this to not change semicolons to commas?
Thanks.
ScottyScottyDMTue, 25 Feb 2014 01:04:56 +0100https://ask.libreoffice.org/en/question/30336/
- LO Spreadsheet: If cell-range contains X, then do Yhttps://ask.libreoffice.org/en/question/27099/lo-spreadsheet-if-cell-range-contains-x-then-do-y/I'm unsure how to do this.
Task: If e.g. cell B12 to B41 contains value "1", then and only then, create SUM of cells C12 to C41 with the value they have (but only for the rows in which the B-column does have a value "1") and enter that value into target cell C 60.
Maybe I better describe what I'm trying to do here: I keep track of something in a table that has colums Mo-So for the weekdays in which I enter a 1 if the event is on that day. Next to that I have columns going from 9-24 which is the hour of the day. If a certain even happens I enter a "1" for that hour of the day.
What I now want is some formula that does the following: Create a nice weekly table for all weekdays and add events at the correct hour they happened.
I'm unsure how to do this. Guess it's possible but the formula needed makes me dizzy and I don't know where to start.nighthomeFri, 13 Dec 2013 15:28:26 +0100https://ask.libreoffice.org/en/question/27099/
- CALC: How to show text within a cell using IF function?https://ask.libreoffice.org/en/question/25287/calc-how-to-show-text-within-a-cell-using-if-function/hello to all,
i am using the IF function and i have to show the content of a cell when the function is true. The text is alphanumeric
i.e
IF (H7>0;B7;""). In case that the cell H7 is bigger than zero, the result of the function should be the text within the cell B7... and the result of the operation is 0 (zero).
Is there any way to show the text within the cell B7?
Thanks, jmoleroFri, 15 Nov 2013 18:09:28 +0100https://ask.libreoffice.org/en/question/25287/
- IF THEN ELSE NESTED returning wrong or improper valueshttps://ask.libreoffice.org/en/question/23577/if-then-else-nested-returning-wrong-or-improper-values/Using a Rather complex IF statement, I'm trying to make a system by having it check a dropdown's value.
Here's the statement.
=IF(A2="Lurker",B2+12+4,IF(A2="Solder",B2+12+2,IF(A2="Skirmisher",B2+12+2,B2+12)))
It's supposed to return a number. It's returning "TRUE" or "FALSE," Not the value intended.XlaitsThu, 03 Oct 2013 21:13:18 +0200https://ask.libreoffice.org/en/question/23577/
- Dynamic formula variableshttps://ask.libreoffice.org/en/question/22598/dynamic-formula-variables/I'm trying to generate a formula in libre that would select a column based on drop down list selection. I realize this is invalid but I think it illustrates what I'm trying to do better than my words could.
=IF($A$2=1:52,C5:BA5,)-(IF($B$2=1:52,C5:BA5,)
Which seemed to me like a logical simplification of this ridiculously long formula that I believe has too many IF's to work.
=IF($A$2=1,C5,(IF($A$2=2,D5,(IF($A$2=3,E5,(IF($A$2=4,F5,(IF($A$2=5,G5,(IF($A$2=6,H5,(IF($A$2=7,I5,(IF($A$2=8,J5,(IF($A$2=9,K5,(IF($A$2=10,L5,(IF($A$2=11,M5,(IF($A$2=12,N5,(IF($A$2=13,O5,(IF($A$2=14,P5,(IF($A$2=15,Q5,(IF($A$2=16,R5,(IF($A$2=17,S5,(IF($A$2=18,T5,(IF($A$2=19,U5,(IF($A$2=20,V5,(IF($A$2=21,W5,(IF($A$2=22,X5,(IF($A$2=23,Y5,(IF($A$2=24,Z5,(IF($A$2=25,AA5,(IF($A$2=26,AB5,(IF($A$2=27,AC5,(IF($A$2=28,AD5,(IF($A$2=29,AE5,(IF($A$2=30,AF5,(IF($A$2=31,AG5,(IF($A$2=32,AH5,(IF($A$2=33,AI5,(IF($A$2=34,AJ5,(IF($A$2=35,AK5(IF($A$2=36,AL5,(IF($A$2=37,AM5,(IF($A$2=38,AN5,(IF($A$2=39,AO5,(IF($A$2=40,AP5,(IF($A$2=41,AQ5,(IF($A$2=42,AR5,(IF($A$2=43,AS5,(IF($A$2=44,AT5,(IF($A$2=45,AU5,(IF($A$2=46,AV5,(IF($A$2=47,AW5,(IF($A$2=48,AX5,(IF($A$2=49,AY5,(IF($A$2=50,AZ5,(IF($A$2=51,BA5,(IF($A$2=52,BB5,))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))
A2 and B2 are drop downs 1-52 which I would like to have the formula insert the cell value from that column/row.
Any help would be appreciated; tried to post a screen shot of the table but alas I lack the karma to upload.
BrianSat, 14 Sep 2013 01:19:51 +0200https://ask.libreoffice.org/en/question/22598/
- Calc using something like nested IF statementshttps://ask.libreoffice.org/en/question/21858/calc-using-something-like-nested-if-statements/I am working on a project and ran into a slight issue, and would love help solving it. The biggest issue is that I am not totally sure what I am asking. I have cells C39 through C45 that need to return values based off of C47 through C67. Basically if C47 is 1 than C39 is 1, if C47 is 2 then C40 is 1, and so on. C47 through C67 can only use values of 1 through 6, and this wouldn't be so hard if each one affected C39-C45 differently.
image:
![The area](http://imageshack.us/a/img543/3264/l8q2.png)
I tried using nested IF statements but that was long and complicated, and I don't happen to know other methods of doing this. I have it working using quite a bit of IF statements on a second sheet, but I would like help condensing it to just the first page in those cells.
Addendum:
Sorry I forgot to mention, but the value of C39 through C45 need to add one based on the values of C47 through C67. So if C47 and C48 are both 1 then C39 needs to be 2. C39 through C45 can be any number.
Addendum:
Sheet 1 is the front-end of the project, and Sheet 2 is where Sheet 1's C39 through C45 are calculated.
C39-C45:
![Front End C39-45](http://i1121.photobucket.com/albums/l505/Skidrow925/KM2013/KMP7.png)
Top purple is for send to front end, the rest is for when C47-67 are 1:
![Rear End C39-45&C47-67](http://i1121.photobucket.com/albums/l505/Skidrow925/KM2013/KMP1.png)
For 2:
![When C47-67 are 2](http://i1121.photobucket.com/albums/l505/Skidrow925/KM2013/KMP2.png)
For 3:
![When C47-67 are 3](http://i1121.photobucket.com/albums/l505/Skidrow925/KM2013/KMP3.png)
For 4:
![When C47-67 are 4](http://i1121.photobucket.com/albums/l505/Skidrow925/KM2013/KMP4.png)
For 5:
![When C47-67 are 5](http://i1121.photobucket.com/albums/l505/Skidrow925/KM2013/KMP5.png)
For 6:
![When C47-67 are 6](http://i1121.photobucket.com/albums/l505/Skidrow925/KM2013/KMP6.png)GuzzlebirdMon, 26 Aug 2013 19:24:39 +0200https://ask.libreoffice.org/en/question/21858/
- How can I insert a value in a cell depending on the value of another cell?https://ask.libreoffice.org/en/question/20784/how-can-i-insert-a-value-in-a-cell-depending-on-the-value-of-another-cell/I have a spreadsheet where column A holds several values ;e.g. MU, AU, EU, MD, AD, ED, CLOSED. "U" stands for "Upstairs", "D" for "Downstairs".
I wish to execute the following pseudocode:
IF cell A'n' = MU or AU or EU
THEN write "upstairs" to cell B'n'
ELSE IF cell A'n' = MD or AD or ED
THEN write "downstairs" to cell B'n'
ELSE write "other" to cell B'n'
ENDIF
ENDIF
I've tried several versions of this code - =IF(D2="DU"or"NU","up","down") - but that gives me Err:508, so I suspect the problem is my ignorance of the proper syntax. Can anyone rectify my ignorance, please? Or point me to a good tutorial, at least.
TIAOttawa ReiverSun, 28 Jul 2013 21:18:52 +0200https://ask.libreoffice.org/en/question/20784/
- Mail Merge - If Statementshttps://ask.libreoffice.org/en/question/18359/mail-merge-if-statements/Does Writer support placeholders - If...Then...Else?
- similar to MS Word
Thankswje132Fri, 31 May 2013 20:16:48 +0200https://ask.libreoffice.org/en/question/18359/
- problem with conditionalhttps://ask.libreoffice.org/en/question/3067/problem-with-conditional/I have a problem with de function "if"
I edit 3 consecutives cells with the same value:
1234 e.g.
B1347 1234
B1348 1234
B1349 1234
in another cell i write the funtion: =IF(B1347=B1348=B1349;1;0)
the function always returns 0, but the values are the same.
What i doing wrong?
P.S:
i created a new spreadsheet and i tried to do the same.
I edited 3 consecutives cells with the same value:
1234 e.g.
A1 1234
A2 1234
A3 1234
in another cell I wrote the function: =IF(B1347=B1348=B1349)
The function returned the value FALSE
but when i edited A1 and put the value 1, the function returned the value TRUE
I tried other combinations, and only when the first cell that appears in the function was 1, the function returns TRUE.
note: i am Brazilian and i use the portuguese version of CalcAndrĂ© CamargoSun, 03 Jun 2012 13:38:33 +0200https://ask.libreoffice.org/en/question/3067/
- Is it possible to use more than one test in the IF() function in Calc?https://ask.libreoffice.org/en/question/13317/is-it-possible-to-use-more-than-one-test-in-the-if-function-in-calc/Hi there
I was wondering if it is possible to use more than one test in the function IF().
What I want to do is changing the value to a fixed value if it drops below 0 or goes above 100.
At the moment, I am using
> =IF([function]<0, [function], 0)
however, I would like to add this element too:
> =IF([function]>100, [function], 100)
Is it possible to do both in one function?
chtfnWed, 27 Feb 2013 07:35:30 +0100https://ask.libreoffice.org/en/question/13317/
- Does Writer support nested IF statements?https://ask.libreoffice.org/en/question/8989/does-writer-support-nested-if-statements/LibreOffice 3.6.4 English (US) on Windows.
Why do I get "** Expression is faulty **" if I try to use nested IFs in writer?
Example:
`<Table2.B4>-<Table2.B3>` is about 1300
=if((<Table2.B4>-<Table2.B3>)<1095; 0.00; 0.15)
gives me
0.15
but
=if((<Table2.B4>-<Table2.B3>)<1095; if((<Table2.B4>-<Table2.B3>)<730; 0.00; 0.1); 0.15)
gives me
** Expression is faulty **InstigaterWed, 12 Dec 2012 21:04:03 +0100https://ask.libreoffice.org/en/question/8989/
- Calc - if then otherwise otherwisehttps://ask.libreoffice.org/en/question/7411/calc-if-then-otherwise-otherwise/Hi, can anybody tell me, how to interlace an if-clause ?
I.e.: =IF(0<=A1<=80;"A";IF(80<=A1<=95;"B";"C")) doesn't work.
Is it necessary to use an other function therefor ? Or is there no possibility to do that ?
Thanks !shadowFri, 02 Nov 2012 13:54:59 +0100https://ask.libreoffice.org/en/question/7411/
- IF [find value in a column] THEN [copy value from a different column]https://ask.libreoffice.org/en/question/4142/if-find-value-in-a-column-then-copy-value-from-a-different-column/Hi,
I have a spreadsheet with 10 columns. Column 1 has ID's, the rest (2 trough 10) have data attached to that ID's. Now I have a new column (column 11) where only **some** of the IDs in column 1 are present and **not** in order.
I want to attach in column 12 data present in column 2 and I need a way to search column 1 until the correct ID (ie: matching the ID in column 11) is found and when this happens, paste the data present in column 2.
Please tell me if I'm not being clear.GabrielTue, 24 Jul 2012 17:33:29 +0200https://ask.libreoffice.org/en/question/4142/