[libreoffice-users] Re: Sum function isfiring in Calc
I use Calc to run a monthly (non-financial) spreadsheet for a small charity I'm secretary to - a page per month, client cases on the vertical, various circumstantial details across the columns, SUM at the bottom of each column, the first of them a simple 'tally' column ( '1' entered for each caes to give a monthly total.) Last month's page had 22 rows. When I came to sum up and report, I realised the tally total wasn't in fact agreeing with the other totals. The same formula applies (they are all drag-copied from an original entry on a page some months back), but the SUM total in the Tally column is simply wrong - out by 1 to everything else. Since it was such a small page, it was easily spotted and allowed for. In a larger page, it might have been a 2-coffee hold-up. I report it more out of curiosity than anything else. Has anyone come across similar instances ? Sincerely Ian Graham Wales UK -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: Missing function
Daniel Devor wrote > The LO instructions refer to "insert-file" but I cannot find any such > in my copy of LO. Please advise how to run this function since my need > is to do just that--insert or add a file to another. LO 5.3.03 Depending on your needs, in addition to the Insert -> Document menu entry... might also look at Insert -> Object -> OLE Object and select the "Create from file" radio button. -- View this message in context: http://nabble.documentfoundation.org/Missing-function-tp4207838p4207862.html Sent from the Users mailing list archive at Nabble.com. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Calc function to return the daynumber weeknumber of the year
Perfect, thanks Miguel! On 7/19/2014 10:04 AM, m.a.riosv miguelange...@libreoffice.org wrote: For weeks they have been always in LibreOffice: WEEKNUM( ) returns ISO 8601 week numbers. WEEKNUM_ADD( ) returns week number as excel. For days: =DAYS(TODAY();DATE(YEAR(TODAY());1;1)-1) I think the best way to find calc functions it's through function wizard: http://nabble.documentfoundation.org/file/n4116065/Captura.png -- View this message in context: http://nabble.documentfoundation.org/Calc-function-to-return-the-daynumber-weeknumber-of-the-year-tp4116056p4116065.html Sent from the Users mailing list archive at Nabble.com. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: Calc function to return the daynumber weeknumber of the year
For weeks they have been always in LibreOffice: WEEKNUM( ) returns ISO 8601 week numbers. WEEKNUM_ADD( ) returns week number as excel. For days: =DAYS(TODAY();DATE(YEAR(TODAY());1;1)-1) I think the best way to find calc functions it's through function wizard: http://nabble.documentfoundation.org/file/n4116065/Captura.png -- View this message in context: http://nabble.documentfoundation.org/Calc-function-to-return-the-daynumber-weeknumber-of-the-year-tp4116056p4116065.html Sent from the Users mailing list archive at Nabble.com. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: sum function in libreoffice calc doesn't seem to work
Bravo! This was just the solution/explanation I needed! joshsimpson josh.simpson at gmail.com writes: So after some experimentation I've hit upon an easy fix for importing CSV files with numbers ... changing the text delimiter to ' and selecting Quoted field as text and Detect special numbers does the trick. Of -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: Calc Function Wizard - IF Result differences
Hi :) Uploading files to Nabble is usually easier. Either click on the links in this email, or navigate from LibreOffice.org (errr, or google-it using LibreOffice Nabble (or something) for your search) Just above the space to entire your Message is a row of buttons including one called More. The top option in More is to upload file and that gives you a Browse option that is a lot like attaching things to emails. However, when you have uploaded the 'attachment' it injects a line of html 'code' into the message and you can move that around a bit if you want. If you know html 'coding' then you can also edit the text that people would click on to reach your attachment but it's usually pretty good at giving it a reasonable name. Regards from Tom :) -- View this message in context: http://nabble.documentfoundation.org/Calc-Function-Wizard-IF-Result-differences-tp4078775p4079017.html Sent from the Users mailing list archive at Nabble.com. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: CONVERT function in Calc
Hi, From user point of view, I don't know but source code seems to be here: http://opengrok.libreoffice.org/xref/core/scaddins/source/analysis/analysishelper.cxx#2506 I don't know much about generating help part but I found this: http://opengrok.libreoffice.org/xref/help/source/text/scalc/01/04060116.xhp#383 Julien -- View this message in context: http://nabble.documentfoundation.org/CONVERT-function-in-Calc-tp4074680p4074963.html Sent from the Users mailing list archive at Nabble.com. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: =IF Function
IF(Test,OK,) shows OK if Test is True and nothing (the cell is blank) if False Hope this helps ;) -- View this message in context: http://nabble.documentfoundation.org/IF-Function-tp4062008p4062015.html Sent from the Users mailing list archive at Nabble.com. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: =IF Function
DOH! Thanks all. I gave you a wonderful chance to point out the bleeding obvious. I tried putting the quote marks around the text and got an error result. That was late last night so I must have done something wrong. Tink. -- View this message in context: http://nabble.documentfoundation.org/IF-Function-tp4062008p4062031.html Sent from the Users mailing list archive at Nabble.com. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: =IF Function
Hi :) Lol, i assumed that after we got the bleeding obvious out the way we would find something more complex than just a late-night coding issue. Still best to get the waggle the wires or switch it off and then switch it on again type answer out the way first. Regards from Tom :) From: Tinkerer j_taylo...@btinternet.com To: users@global.libreoffice.org Sent: Tuesday, 18 June 2013, 11:22 Subject: [libreoffice-users] Re: =IF Function DOH! Thanks all. I gave you a wonderful chance to point out the bleeding obvious. I tried putting the quote marks around the text and got an error result. That was late last night so I must have done something wrong. Tink. -- View this message in context: http://nabble.documentfoundation.org/IF-Function-tp4062008p4062031.html Sent from the Users mailing list archive at Nabble.com. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: A function doesn't work on cell whihc is a link to another cell
Hi Michael, I don't know why happen that, that it didn't work once, or twice some few days back, but now it works. Thank you for help and support! - Best Regards from Pál -- View this message in context: http://nabble.documentfoundation.org/A-function-doesn-t-work-on-cell-whihc-is-a-link-to-another-cell-tp4061013p4061324.html Sent from the Users mailing list archive at Nabble.com. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: =IF Function - SOLVED
I could not get your prompts to work, but they gave me ideas. The final formula was: =IFERROR(C30*100/B30,) This also covered other errors where only one number was present, so no calculation was possible. Many thanks, Tink. -- View this message in context: http://nabble.documentfoundation.org/IF-Function-tp4043493p4043543.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: =IF Function
Thank you Brian I'd forgotten that there is a material difference between Empty cells and Blank cells, even though I noticed that the result sometimes varied. Tink. -- View this message in context: http://nabble.documentfoundation.org/IF-Function-tp4043493p4043610.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: regexp function
Am 09.10.2012 12:24, Tom Davies wrote: Hi :) Is there not some way to identify how far along the letter is through one of the count functions? If so then a +1 should give the required value? In the interests of release early and release often it might be faster for you to 'just' do a macro since you already have the skill-set for that. Regards from Tom :) A Python extension might be the most simple solution. Python supports the same well known and sensible regex syntax as the Perl language does. All it takes is a set of wrapper routines to handle the input string, the regular expressions and extra options distributed with some xml glue to register the functions as office components. -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: Calc function
Am 17.04.2012 19:35, Mike Phelan wrote: I am trying to automatically remove a number from a range of cells, if it appears in a different range. I am creating a spreadsheet where once a number appears in B4:G11 4 times, it changes color to Red...got that part. Then I want to manually enter that number somewhere into J4:N21. once entered into J4:N21, i want that number to erase from B4:G11. ideally at that point i would also like all remaining numbers in B4:G11 to shift up, leaving no empty cells above cells with values in them (keeping them in the order entered within columns). Only a small set of features adds data to your spreadsheet and nothing in Calc will ever remove any data automatically. You have to do that by hand or by means of a macro program. It seems to be another inventory task which should be done in a database program of your choice. Later you may connect your database to this office suite (this is what the Base component does) and use Calc or Writer as output medium for pretty printing and further calculations. -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Calc function
Den 18 april 2012 09:47 skrev Andreas Säger ville...@t-online.de: Am 17.04.2012 19:35, Mike Phelan wrote: I am trying to automatically remove a number from a range of cells, if it appears in a different range. I am creating a spreadsheet where once a number appears in B4:G11 4 times, it changes color to Red...got that part. Then I want to manually enter that number somewhere into J4:N21. once entered into J4:N21, i want that number to erase from B4:G11. ideally at that point i would also like all remaining numbers in B4:G11 to shift up, leaving no empty cells above cells with values in them (keeping them in the order entered within columns). Only a small set of features adds data to your spreadsheet and nothing in Calc will ever remove any data automatically. You have to do that by hand or by means of a macro program. It seems to be another inventory task which should be done in a database program of your choice. Later you may connect your database to this office suite (this is what the Base component does) and use Calc or Writer as output medium for pretty printing and further calculations. I actually think it's possible with cell functions only, but I think it would be quite complicated. Too complicated for me, anyway. I did something else that I also thought was impossible first, but I solved it eventually. It was not easy (for me) though… My task was to create a list of things that is sorted automatically. The formulas were quite long and hard to read… Kind regards Johnny Rosenberg ジョニー・ローゼンバーグ -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: Calc function
Am 18.04.2012 17:56, Johnny Rosenberg wrote: Den 18 april 2012 09:47 skrev Andreas Sägerville...@t-online.de: Am 17.04.2012 19:35, Mike Phelan wrote: I am trying to automatically remove a number from a range of cells, if it appears in a different range. I am creating a spreadsheet where once a number appears in B4:G11 4 times, it changes color to Red...got that part. Then I want to manually enter that number somewhere into J4:N21. once entered into J4:N21, i want that number to erase from B4:G11. =IF(COUNTIF(B4;$J$4:$N$21);;B4) copy down 7 rows and 5 columns to the right. Copy the resulting range and paste over B4:G11 ideally at that point i would also like all remaining numbers in B4:G11 to shift up, leaving no empty cells above cells with values in them (keeping them in the order entered within columns). Sort each of the 6 columns manually. -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: sum function in libreoffice calc doesn't seem to work
Hi :) Different devices or programs might decide to use different ways of defining what a column contains and might also vary as to whether it treats a particular column as a number or as text. Typically i would open a Csv file with a text-editor such as Scite, Gedit or even Notepad (if that's all i have) just to have a look at how the info is presented. Searchreplace is often useful. Regards from Tom :) -- View this message in context: http://nabble.documentfoundation.org/sum-function-in-libreoffice-calc-doesn-t-seem-to-work-tp1839208p3479861.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: sum function in libreoffice calc doesn't seem to work
Get the Extension CT2N, which is convert text2 Numbers. You only jump through one loop then. Tink. -- View this message in context: http://nabble.documentfoundation.org/sum-function-in-libreoffice-calc-doesn-t-seem-to-work-tp1839208p3481046.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: sum function in libreoffice calc doesn't seem to work
So after some experimentation I've hit upon an easy fix for importing CSV files with numbers ... changing the text delimiter to ' and selecting Quoted field as text and Detect special numbers does the trick. Of course you have to save the file before you can SUM or do anything else. The nice thing is once you change these settings once they become the default setting. -- View this message in context: http://nabble.documentfoundation.org/sum-function-in-libreoffice-calc-doesn-t-seem-to-work-tp1839208p3478365.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: sum function in libreoffice calc doesn't seem to work
I'm having this same problem and I must admit, this is a deal breaker for LibreCalc for me and possibly for Linux since I can't use VMWare on kernel 3.0.o.1. Going to try Google Docs but I am stunned that LibreOffice makes you jump through so many hoops to using the SUM function on a column of numbers imported from CSV. -- View this message in context: http://nabble.documentfoundation.org/sum-function-in-libreoffice-calc-doesn-t-seem-to-work-tp1839208p3477876.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: NPV Function in Calc
Hi. I think it is the way it is used. Say in your formula you have something worth 2000 in 5 years at 8%, you would write; NPV = 2000/(1+0.08)^5 =1361.17 In LO the function is for a series of payments, you have 1 payment at 5 years, you would write; =NPV(0.08;0;0;0;0;2000) = 1361.17 steve On 19/03/11 10:04, NoOp wrote: On 03/18/2011 10:28 AM, Andrew Priebe wrote: Hello, I recently upgraded from OpenOffice and everything seems to be working great. I do have a question about the built-in NPV function in Calc however. Calculating the NPV of a given cash flow using the built-in function and doing a manual calculation provide drastically different results. I am not sure what the built-in function uses, but I am using the fairly standard formula of: (Value of Cash flow at period t) / (1 + r) ^ t Is this expected behavior? I am using a Debian build of version 3.3.1. What specifically are you entering in the NPV formula? These might help: http://help.libreoffice.org/Calc/Financial_Functions_Part_Two#NPV http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Calc:_NPV_function try these examples: http://office.microsoft.com/en-us/excel-help/npv-HP005209199.aspx -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org List archive: http://listarchives.libreoffice.org/www/users/ *** All posts to this list are publicly archived for eternity ***
Re: [libreoffice-users] Re: NPV Function in Calc
Hello again, After running a few different examples, I have confirmed with some classmates that in this instance it is a publishing error in my textbook (what I was comparing against initially) and in the case of my own calculations, my t value was off by one. Sorry for any confusion! Thanks for the help! 2011/3/19 Gérard Fargeot gerard.farg...@orange.fr: Andrew Priebe wrote: Hello, (Value of Cash flow at period t) / (1 + r) ^ t -- Best regards, Andrew Priebe Hi, You are using the wrong function. Don't used NPV but PV : =PV(0,08;5;0;2000) Gérard -- View this message in context: http://nabble.documentfoundation.org/NPV-Function-in-Calc-tp2698843p2701608.html Sent from the Users mailing list archive at Nabble.com. -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org List archive: http://listarchives.libreoffice.org/www/users/ *** All posts to this list are publicly archived for eternity *** -- Best regards, Andrew Priebe -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org List archive: http://listarchives.libreoffice.org/www/users/ *** All posts to this list are publicly archived for eternity ***
[libreoffice-users] Re: Missing function: Bankers Rounding
If you don't want any errors even in the 10th digit, I think it's probably a good idea to use ROUNDUP and ROUNDDOWN instead of adding or subtracting 0.005 for the final result. So, my final function for a Bankers Round that I'm using is: =IF(ROUND(100*A1-INT(100*A1),8)=0.5,IF(MOD(ROUND(100*A1-0.5,0),2),ROUNDUP(A1,2),ROUNDDOWN(A1,2)),ROUND(A1,2)) Kind of repetitious with all the ROUND functions, but works and gives an exact match to GnuCash results witch was what I needed. Usually I'm inserting something like A1/4 instead of just A1 where the banker's round of A1/4 is my personal expenses and the difference of that and A1 is my business expense. Without any rounding, all the data seemed to match between GnuCash and LibreOffice Calc, but the totals would be off by a few pennies - not acceptable because often a mismatch like that points to an oversight on my part in entering values into Calc. To my surprise though, simple rounding to 2 digits didn't work either. Eventually, I realized GnuCash was using this bankers rounding method. Does anyone know how to create a user-defined function in LibreOffice Calc so I could just enter BANKROUND(A1) instead of the messy function listed above? -- View this message in context: http://nabble.documentfoundation.org/Missing-function-Bankers-Rounding-tp2530641p2669358.html Sent from the Users mailing list archive at Nabble.com. -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org List archive: http://listarchives.libreoffice.org/www/users/ *** All posts to this list are publicly archived for eternity ***
[libreoffice-users] Re: Missing function: Bankers Rounding
colinkeenan wrote: plino wrote: I had never heard of bankers rounding before. Interesting concept. There is no such function in OOo/LO, Excel or Gnumeric... But you can easily create a function =IF(A1-INT(A1)=0.5;IF(ISEVEN(INT(A1));A1-0.5;A1+0.5);ROUND(A1)) (Adjust if your separator is a comma instead of a semi-colon) What this does is check if the fractional part is 0.5 and adds 0.5 to the number if the integer is odd and subtracts if it is even. If it is not 0.5 then it uses the regular Round() function ;) Hope this helps! Due to shortcomings in LibreOffice Calc, I had to adjust your formula as follows: =IF(ROUND(A1-INT(A1),8)=0.5,IF(MOD(A1-0.5,2),A1+0.5,A1-0.5),ROUND(A1)) and for the more usual case of needing to do a Bankers Round to a penny and not a dollar, I am actually using this: =IF(ROUND(100*A1-INT(100*A1),8)=0.5,IF(MOD(100*A1-0.5,2),A1+0.005,A1-0.005),ROUND(A1,2)) The reason I needed to use ROUND... in the test is that LibraOffice Calc seems to often come up with numbers like .4... when evaluating A1-INT(A1) instead of .5, causing the test to fail when it should work. The reason I needed to use MOD... instead of ISEVEN(INT... is that after closing and opening the file, anywhere Calc needed to evaluate IF(...IF(ISEVEN(INT... it gave a #MACRO? error. Maybe LibraOffice can't handle nesting functions that far. By using MOD, it doesn't nest as far. Also, MOD(x,2) returns 0 for even and 1 for odd, so it's really replacing ISODD..., and so I had to add .5 instead of subtract .5 when MOD... is true. And of course, the reason for multiplying by 100 is to use the same idea for pennies instead of dollars. Just now, I realized MOD(A1-.5,2) wasn't always acting right either due to LibreOffice Calc not getting the exact result on subtraction. Changing it to MOD(ROUND(A1-.5),2) fixes the problem. I don't know why MOD(ROUND... works when ISEVEN(INT... cause the #MACRO? error. They both use the same level of nested functions. -- View this message in context: http://nabble.documentfoundation.org/Missing-function-Bankers-Rounding-tp2530641p2667055.html Sent from the Users mailing list archive at Nabble.com. -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org List archive: http://listarchives.libreoffice.org/www/users/ *** All posts to this list are publicly archived for eternity ***
[libreoffice-users] Re: Missing function: Bankers Rounding
plino wrote: I had never heard of bankers rounding before. Interesting concept. There is no such function in OOo/LO, Excel or Gnumeric... But you can easily create a function =IF(A1-INT(A1)=0.5;IF(ISEVEN(INT(A1));A1-0.5;A1+0.5);ROUND(A1)) (Adjust if your separator is a comma instead of a semi-colon) What this does is check if the fractional part is 0.5 and adds 0.5 to the number if the integer is odd and subtracts if it is even. If it is not 0.5 then it uses the regular Round() function ;) Hope this helps! Due to shortcomings in LibreOffice Calc, I had to adjust your formula as follows: =IF(ROUND(A1-INT(A1),8)=0.5,IF(MOD(A1-0.5,2),A1+0.5,A1-0.5),ROUND(A1)) and for the more usual case of needing to do a Bankers Round to a penny and not a dollar, I am actually using this: =IF(ROUND(100*A1-INT(100*A1),8)=0.5,IF(MOD(100*A1-0.5,2),A1+0.005,A1-0.005),ROUND(A1,2)) The reason I needed to use ROUND... in the test is that LibraOffice Calc seems to often come up with numbers like .4... when evaluating A1-INT(A1) instead of .5, causing the test to fail when it should work. The reason I needed to use MOD... instead of ISEVEN(INT... is that after closing and opening the file, anywhere Calc needed to evaluate IF(...IF(ISEVEN(INT... it gave a #MACRO? error. Maybe LibraOffice can't handle nesting functions that far. By using MOD, it doesn't nest as far. Also, MOD(x,2) returns 0 for even and 1 for odd, so it's really replacing ISODD..., and so I had to add .5 instead of subtract .5 when MOD... is true. And of course, the reason for multiplying by 100 is to use the same idea for pennies instead of dollars. -- View this message in context: http://nabble.documentfoundation.org/Missing-function-Bankers-Rounding-tp2530641p2667012.html Sent from the Users mailing list archive at Nabble.com. -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org List archive: http://listarchives.libreoffice.org/www/users/ *** All posts to this list are publicly archived for eternity ***
[libreoffice-users] Re: Missing function: Bankers Rounding
What I meant was: if the original numbers have a mixed number of decimal cases you can't apply my solution because of the base 2 calculations. But I assume that in a table you don't have values with 3 decimal cases mixed with one decimal case? Therefore you could go from 4 decimal cases to 1 in 3 steps using my method... In any case, the short answer is: there isn't such a round function in any of the 3 spreadsheets. Apparently the round() function in Visual Basic for Applications (VBA) does a bankers' rounding. http://groups.google.com/group/microsoft.public.excel.charting/msg/107fce6145b70d69?pli=1 Hope this helps -- View this message in context: http://nabble.documentfoundation.org/Missing-function-Bankers-Rounding-tp2530641p2533054.html Sent from the Users mailing list archive at Nabble.com. -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org List archive: http://listarchives.libreoffice.org/www/users/ *** All posts to this list are publicly archived for eternity ***
Re: [libreoffice-users] Re: Missing function: Bankers Rounding
On 19/02/11 00:38, Robert Prins wrote: On Sat, Feb 19, 2011 at 00:24, plinopedl...@gmail.com wrote: I had never heard of bankers rounding before. Interesting concept. There is no such function in OOo/LO, Excel or Gnumeric... But you can easily create a function =IF(A1-INT(A1)=0.5;IF(ISEVEN(INT(A1));A1-0.5;A1+0.5);ROUND(A1)) (Adjust if your separator is a comma instead of a semi-colon) What this does is check if the fractional part is 0.5 and adds 0.5 to the number if the integer is odd and subtracts if it is even. If it is not 0.5 then it uses the regular Round() function ;) Hope this helps! Yes, but mostly no... The above works for 22.5 and -1234.5, but now I want to deal with currencies... Like $ 123.455 or € 99.125, which should be rounded to $ 123.46 and € 99.12 In other words, the issue is slightly more complicated... Robert All you need to do is to set the cell format to be 2 decimal places. If you then enter 123.455 into a cell that will round up to 123.46 automatically for youno need for any sort of complicated formulae -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org List archive: http://listarchives.libreoffice.org/www/users/ *** All posts to this list are publicly archived for eternity ***
Re: [libreoffice-users] Re: Missing function: Bankers Rounding
Hi all, Andreas Säger schrieb: Am 19.02.2011 00:50, Robert Prins wrote: Why is there no standard function to do bankers rounding (aka round-to-even)? http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Calc:_MROUND_function Indeed, MROUND is not a standard function. It is part of the (always installed) Analysis add-on. I think, MROUND will work for Robert. Examples: Round to nearest even Cent: MROUND(12.354;0.02) results in 12.36 MROUND(-12.328;0.02) results in -12.32 Round-to-even to one decimal place MROUND(3.49;0.2) results in 3.4 MROUND(2.51;0.2) results in 2.6 Kind regards Regina -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org List archive: http://listarchives.libreoffice.org/www/users/ *** All posts to this list are publicly archived for eternity ***
[libreoffice-users] Re: Missing function: Bankers Rounding
I had never heard of bankers rounding before. Interesting concept. There is no such function in OOo/LO, Excel or Gnumeric... But you can easily create a function =IF(A1-INT(A1)=0.5;IF(ISEVEN(INT(A1));A1-0.5;A1+0.5);ROUND(A1)) (Adjust if your separator is a comma instead of a semi-colon) What this does is check if the fractional part is 0.5 and adds 0.5 to the number if the integer is odd and subtracts if it is even. If it is not 0.5 then it uses the regular Round() function ;) Hope this helps! -- View this message in context: http://nabble.documentfoundation.org/Missing-function-Bankers-Rounding-tp2530641p2530764.html Sent from the Users mailing list archive at Nabble.com. -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org List archive: http://listarchives.libreoffice.org/www/users/ *** All posts to this list are publicly archived for eternity ***
Re: [libreoffice-users] Re: Missing function: Bankers Rounding
On Sat, Feb 19, 2011 at 00:24, plino pedl...@gmail.com wrote: I had never heard of bankers rounding before. Interesting concept. There is no such function in OOo/LO, Excel or Gnumeric... But you can easily create a function =IF(A1-INT(A1)=0.5;IF(ISEVEN(INT(A1));A1-0.5;A1+0.5);ROUND(A1)) (Adjust if your separator is a comma instead of a semi-colon) What this does is check if the fractional part is 0.5 and adds 0.5 to the number if the integer is odd and subtracts if it is even. If it is not 0.5 then it uses the regular Round() function ;) Hope this helps! Yes, but mostly no... The above works for 22.5 and -1234.5, but now I want to deal with currencies... Like $ 123.455 or € 99.125, which should be rounded to $ 123.46 and € 99.12 In other words, the issue is slightly more complicated... Robert -- Robert AH Prins robert.ah.pr...@gmail.com -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org List archive: http://listarchives.libreoffice.org/www/users/ *** All posts to this list are publicly archived for eternity ***
[libreoffice-users] Re: Missing function: Bankers Rounding
Am 19.02.2011 00:50, Robert Prins wrote: Why is there no standard function to do bankers rounding (aka round-to-even)? http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Calc:_MROUND_function Indeed, MROUND is not a standard function. It is part of the (always installed) Analysis add-on. -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org List archive: http://listarchives.libreoffice.org/www/users/ *** All posts to this list are publicly archived for eternity ***
[libreoffice-users] Re: Missing function: Bankers Rounding
Am 19.02.2011 02:25, Andreas Säger wrote: Am 19.02.2011 00:50, Robert Prins wrote: Why is there no standard function to do bankers rounding (aka round-to-even)? http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Calc:_MROUND_function Indeed, MROUND is not a standard function. It is part of the (always installed) Analysis add-on. SORRY, forget my MROUND suggestion. It has nothing to do with banker's rounding. -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org List archive: http://listarchives.libreoffice.org/www/users/ *** All posts to this list are publicly archived for eternity ***
Re: [libreoffice-users] Re: Missing function: Bankers Rounding
On Sat, Feb 19, 2011 at 01:05, plino pedl...@gmail.com wrote: It's only slightly more complicated :) =IF(VALUE(RIGHT(A9))=5;IF(ISEVEN(VALUE(LEFT(RIGHT(A9;2;A9-0.005;A9+0.005);ROUND(A9;2)) Check if the last digit is 5. If it is add 0.005 if the previous digit is odd or subtract if it is even. Otherwise use the standard round function with two cases. Now, if the numbers of decimal cases is not constant, then it would be complicated :) Actually, that is exactly what I am looking for, a round function that allows me to do bankers rounding on *any* decimal position I choose. I have files where I need to round-to-even to one decimal place... Robert -- Robert AH Prins robert.ah.pr...@gmail.com -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org List archive: http://listarchives.libreoffice.org/www/users/ *** All posts to this list are publicly archived for eternity ***
[libreoffice-users] Re: sum function in libreoffice calc doesn't seem to work
plino pedlino at gmail.com writes: Bill, do the cells in the range contain only natural numbers? I suspect that you are having a problem with the decimal separator. Your cells are probably identified as text because of that (are the values aligned to the left?) Paste this in cell D2 to check =VALUE(C2) Hello, Plino: This is a second reply. After I wrote you earlier, I tried re-importing the .csv file. This time, I clicked on the Standard heading on top of the column in question, hoping there would be a format for Currency. There was none, but after I clicked Hidden then clicked back to Standard, the file imported with the currencies properly recognized and aligned to the right. Now the =SUM(c2:c46) function works just fine. I am sorry for wasting so many people's time, but I could not figure this out by myself. I am very grateful to everyone involved for their suggestions. One final question: Shouldn't the Import function recognize currencies automatically rather than making the user jump through these hoops? Sincerely/Bill Woodruff -- E-mail to users+h...@libreoffice.org for instructions on how to unsubscribe List archives are available at http://www.libreoffice.org/lists/users/ All messages you send to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: sum function in libreoffice calc doesn't seem to work
Bill, do the cells in the range contain only natural numbers? I suspect that you are having a problem with the decimal separator. Your cells are probably identified as text because of that (are the values aligned to the left?) Paste this in cell D2 to check =VALUE(C2) -- View this message in context: http://nabble.documentfoundation.org/sum-function-in-libreoffice-calc-doesn-t-seem-to-work-tp1839208p1847724.html Sent from the Users mailing list archive at Nabble.com. -- E-mail to users+h...@libreoffice.org for instructions on how to unsubscribe List archives are available at http://www.libreoffice.org/lists/users/ All messages you send to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: sum function in libreoffice calc doesn't seem to work
Actually it depends on your Language Settings. And LibreOffice inherited the problems from OpenOffice... E.g. for Portuguese (European) the decimal separator is a comma(,) but if I prefer to use a point (because most international publications are formatted according to US notation) when I disable the box Same as locale setting it will type the point and as soon as I press enter it will convert it to a date. This is a clear mistake since a point is not a separator in Portugal... i.e. typing 2.3 is converted to 02-03-2010 which is value 40239 -- View this message in context: http://nabble.documentfoundation.org/sum-function-in-libreoffice-calc-doesn-t-seem-to-work-tp1839208p1843520.html Sent from the Users mailing list archive at Nabble.com. -- E-mail to users+h...@libreoffice.org for instructions on how to unsubscribe List archives are available at http://www.libreoffice.org/lists/users/ All messages you send to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: sum function in libreoffice calc doesn't seem to work
Am 03.11.2010 23:12, bill woodruff wrote: mouse. And when I manually insert the range (i.e., =SUM(C2,C46)) it returns a result of 0 (zero). You try to sum numeric text (a sequence of digits). -- E-mail to users+h...@libreoffice.org for instructions on how to unsubscribe List archives are available at http://www.libreoffice.org/lists/users/ All messages you send to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: sum function in libreoffice calc doesn't seem to work
Mark mhullrich at gmail.com writes: A sum range is =SUM(C2:C46) - could that be (part of) it? Mark Hello, Mark: Thank you for your prompt reply. I apologize, but I mis-punctuated my original message. The formula, as you correctly observe, is =SUM(C2:C46) --and that's pasted directly from the spreadsheet. However, it still returns a result of 0 (zero). I've been working with spreadsheets (mostly Excel) since the days of Lotus Symphony. I had high hopes for LibreOffice and have had good results with the document package so far. But this spreadsheet behavior is driving me nuts. I would appreciate any suggestions. Would you like me to e-mail you a copy of the spreadsheet? Sincerely/Bill Woodruff -- E-mail to users+h...@libreoffice.org for instructions on how to unsubscribe List archives are available at http://www.libreoffice.org/lists/users/ All messages you send to this list will be publicly archived and cannot be deleted
