Re: [libreoffice-users] Calc Function Wizard - IF Result differences
Hi, Entering a formula into the Function Wizard in Calc shows up two different answers in the 'Result' and 'Function Result' answer box. If the editing cursor is placed in a certain section of the formula, only the 'Function Result' answer box holds the correct answer i.e. 751.52004. Pressing 'OK' puts the 'Result' answer box value in the calculated cell, which unfortunately is the wrong answer i.e. 751.25. I have looked at the help file to see if there was a difference between the Result answer boxes but found none. The initial formula is: =IF(0G4=$G$20,G4*$F$20,IF($G$20G4=$J$20,(G4-$G$20)*$I$20+($F$20*$G$20),IF($J$20G4=$M$20,(G4-$G$20-$J$20)*$L$20+(($G$20*$F$20)+($J$20*$I$20),IF($M$20G4=$P$20,(G4-$G$20-$J$20-$M$20)*$O$20+(($G$20*$F$20)+($J$20*$I$20)+($M$20*$L$20),IF(G4$P$20,((G4-$G$20-$J$20-$M$20-$P$20)*$O$20+(($G$20*$F$20)+($J$20*$I$20)+($M$20*$L$20)+($O$20*$P$20)) In addition I have tried changing the formula without success to try and reduce the multiple nested IF functions: i.e.: =sum((IF(0G4=$G$20,G4*$F$20,0),(IF($G$20G4=$J$20,(G4-$G$20)*$I$20+($F$20*$G$20),0),(IF($J$20G4=$M$20,(G4-$G$20-$J$20)*$L$20+(($G$20*$F$20)+($J$20*$I$20),0),(IF($M$20G4=$P$20,(G4-$G$20-$J$20-$M$20)*$O$20+(($G$20*$F$20)+($J$20*$I$20)+($M$20*$L$20),0),(IF(G4$P$20,((G4-$G$20-$J$20-$M$20-$P$20)*$O$20+(($G$20*$F$20)+($J$20*$I$20)+($M$20*$L$20)+($O$20*$P$20)),0) The SUMIF function was also tried but my brain just gave up. I cannot see how the VLOOKUP function will help either as I am not looking up values from cells, I am calculating a single value in H4 based in a variable input in G4. Have a peak at the uploaded spreadsheet on Nabble: http://nabble.documentfoundation.org/file/n4077843/Electricity.ods You will see that provided you enter a value for G4 below 600 the correct answer is shown i.e. for 600 in G4 H4=750. The moment G4 exceeds 600, even by 1, there is a problem. There must be an answer and or my formula syntax is wrong unbeknown to me. Help would be very much appreciated Sincerely Hylton -- The spreadsheet structure you are using is not so adequate for this calculation. I have a suggestion for your formula in H4. But, before I post it, you should know that there are better possibilities, and that my suggestion doesn't solve all your problems in that spreadsheet. Additionally, the formula I am suggesting might not be optimized, and it is certainly not the only solution. Anyway, in your posted spreadsheet, Electricity.ods - Data2 sheet - Cell H4, I suggest the following formula as one possibility: =(0G4)*(G4=$G$20)*(G4*$F$20)+($G$20G4)*(G4=$G$20+$J$20)*($G$20*$F$ 20+(G4-$G$20)*$I$20)+($G$20+$J$20G4)*(G4=$G$20+$J$20+$M$20)*($G$20*$ F$20+$J$20*$I$20+(G4-($G$20+$J$20))*$L$20)+($G$20+$J$20+$M$20G4)*($G$ 20*$F$20+$J$20*$I$20+$M$20*$L$20+(G4-($G$20+$J$20+$M$20))*$O$20 Hopefully, the email width format won't mess up my intention (a reply to this email probably will). Best Regards, Ady. -- 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: RTF
Le 19/10/2013 10:43, Patrick a écrit : Hi Patrick, I've received this .rtf file (attachment) with a little picture on the left side that i can't see with Libre Office. I'm using Ubuntu because I hate Microsoft and all their products :) ... So, in conclusion, my friend (on Microsoft Office Word) opened this file and the picture was here. I'm asking now, could this problem be resolved in the future Libre Office release? The problem is that many software editors, instead of sticking to the original RTF specification, have created their own added features, differences, etc, Microsoft being one of the particularly pernicious companies that has embraced and expanded the RTF specification. This means that it is very difficult for LibreOffice and other RTF import software to actually do this correctly, and while things are improving, the RTF filters still have (and always have had, even in the days of Sun StarOffice), bugs. The solution to your problem might lie in using a newer version of LibreOffice, but if you are on Ubuntu 13.04 (and unless you are using the just released 13.10), then your version of LibreOffice will be somewhere between 3.6 and 4.0.2. You should also try looking in bugzilla or Ubuntu's launchpad to see whether your particular bug has already been registered, and possibly find out whether someone is working on fixing it. Alex -- 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] Calc IF problem
At 23:45 19/10/2013 +0200, Paul Steyn wrote: On Sat, 19 Oct 2013 15:46:56 +0100 Brian Barker wrote: Remember that multiplication precedes addition and subtraction in evaluation, so many of your parenthesis pairs are unnecessary. You may think they clarify things, but in my experience they obfuscate the meaning of a formula and mask errors. While I agree with Brian's other points, I just wanted to comment that normally, I find it much better to include parentheses around sums, ... Sums? But sums (along with differences) often do need parentheses, of course: it's products and quotients that often don't. ... explicitly stating the order, rather than relying on (sometimes differing) operator precedence rules. Sometimes differing?! Is this the new mathematics? ;^) This also helps to group things, and in my opinion usually makes things more clear, rather than obfuscating anything. Chacun à son goût, of course. Brian Barker -- 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] Calc Function Wizard - IF Result differences
At 14:36 19/10/2013 +0200, Hylton Conacher wrote: The initial formula is: =IF(0G4=$G$20,G4*$F$20,IF($G$20G4=$J$20,(G4-$G$20)*$I$20+($F$20*$G$20),IF($J$20G4=$M$20,(G4-$G$20-$J$20)*$L$20+(($G$20*$F$20)+($J$20*$I$20),IF($M$20G4=$P$20,(G4-$G$20-$J$20-$M$20)*$O$20+(($G$20*$F$20)+($J$20*$I$20)+($M$20*$L$20),IF(G4$P$20,((G4-$G$20-$J$20-$M$20-$P$20)*$O$20+(($G$20*$F$20)+($J$20*$I$20)+($M$20*$L$20)+($O$20*$P$20)) In addition I have tried changing the formula without success to try and reduce the multiple nested IF functions: i.e.: =sum((IF(0G4=$G$20,G4*$F$20,0),(IF($G$20G4=$J$20,(G4-$G$20)*$I$20+($F$20*$G$20),0),(IF($J$20G4=$M$20,(G4-$G$20-$J$20)*$L$20+(($G$20*$F$20)+($J$20*$I$20),0),(IF($M$20G4=$P$20,(G4-$G$20-$J$20-$M$20)*$O$20+(($G$20*$F$20)+($J$20*$I$20)+($M$20*$L$20),0),(IF(G4$P$20,((G4-$G$20-$J$20-$M$20-$P$20)*$O$20+(($G$20*$F$20)+($J$20*$I$20)+($M$20*$L$20)+($O$20*$P$20)),0) The SUMIF function was also tried but my brain just gave up. I cannot see how the VLOOKUP function will help either as I am not looking up values from cells, I am calculating a single value in H4 based in a variable input in G4. Have a peek at the uploaded spreadsheet on Nabble: You can do what you need using VLOOKUP(), seeking values from a small table (much as you already have in rows 58 to 61). And you can probably more easily use MATCH(), which works fairly similarly, but returns a pointer instead of a value. But both of these methods suffer from the problem that you need to seek multiple values from your table in each calculation, so the formulae become rather cumbersome, with a necessity to retrieve essentially the same information repeatedly. It's worth saying that it's generally not a good idea to repeat similar information - as you have at lines 21 to 32. Instead, either use values from a minimal table (as already mentioned) or embed the values in the formulae. Let's redraw your table more simply - say in rows 71 to 74 of columns A and B: 1.25 150 1.25 350 1.25 600 1.52 (Nothing is needed in A71.) Then you can calculate what you need using something like: =MIN(G4;$A$72)*$B$71 +MAX(0;(MIN(G4;$A$73)-$A$72)*$B$72) +MAX(0;(MIN(G4;$A$74)-$A$73)*$B$73) +MAX(0;(G4-$A$74)*$B$74) where G4 is your sample data cell. Each line of this formula (I've split it only for ease of reading, of course) calculates the contribution to the cost from one of the four price bands. Each MIN() function ensures that the upper limit of the quantity is either the value itself or the upper limit of the band - whichever is smaller. So where are the IFs to test which bands are needed?, you ask. Well, each unnecessary contribution would be calculated here (wrongly) as negative, so the MAX() functions, with their first arguments of zero, cancel these incorrect contributions. Try it: it gives the answers you want. I trust this helps. Brian Barker -- 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] RTF
You can open these programs in Notepad ... (1) the simple one comes on all MsFt machines (if it's not present, download it from an older machine) or (2) there are a couple notepads available which include the ability to view images as well; can be easily located safely downloaded through http://www.techsupportalert.com/ Hoping this helps, From: Patrick pdema...@yahoo.com Date: Sat, Oct 19, 2013 at 3:43 AM Subject: [libreoffice-users] RTF To: users@global.libreoffice.org users@global.libreoffice.org Hello, I've received this .rtf file (attachment) with a little picture on the left side that i can't see with Libre Office. I'm using Ubuntu because I hate Microsoft and all their products :) ... So, in conclusion, my friend (on Microsoft Office Word) opened this file and the picture was here. I'm asking now, could this problem be resolved in the future Libre Office release? Best regards, Patrick Demarki -- 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] Calc IF problem
At 21:01 20/10/2013 +0200, Paul Steyn wrote: http://en.wikipedia.org/wiki/Order_of_operations lists a couple of exception cases. Interesting! Thanks. Brian Barker -- 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] RTF
On 10/19/2013 07:08 PM, Mark LaPierre wrote: On 10/19/2013 04:43 AM, Patrick wrote: Hello, I've received this .rtf file (attachment) with a little picture on the left side that i can't see with Libre Office. I'm using Ubuntu because I hate Microsoft and all their products :) ... So, in conclusion, my friend (on Microsoft Office Word) opened this file and the picture was here. I'm asking now, could this problem be resolved in the future Libre Office release? Best regads, Patrick Demarki dipl.ing. Hey Patrick, RTF stands for Rich Text File. Many email tools can handle RTF files. Try opening the file with whatever mail tool you have available to see what happens. To keep the records straight, RTF is an acronym for Rich Text _Format_, not File. https://en.wikipedia.org/wiki/Rich_Text_Format Girvin Herr -- 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] Calc Function Wizard - IF Result differences
Ok, so I just used a file hosting service found via Google search. Here's the file I modified with my two solutions: http://www.filedropper.com/electricity_1 It didn't work the first time or two that I tried to download the file to check, not sure why, but it worked the third time, so maybe persevere if it doesn't work first time for you. When I tried to open the file again, I got told the security settings prevented macros from running, and the cells that used the user function to calculate your answer showed #VALUE, I think it was. You can reduce the security settings to allow macros to run, of course, but perhaps this is not the best way forward after all, although it is still an elegant solution for some values of elegant. So if using just formulas, I liked Brian's use of MAX(0, value) instead of my IF(value 0, value, 0), so I would probably substitute that first off, to make the formulas I've used more legible. I would still strongly recommend intermediate steps for each line of Brian's formula, and then one simple SUM to get the final answer. =MIN(G4;$A$72)*$B$71 +MAX(0;(MIN(G4;$A$73)-$A$72)*$B$72) +MAX(0;(MIN(G4;$A$74)-$A$73)*$B$73) +MAX(0;(G4-$A$74)*$B$74) Also, while Brian has, as I understand it (without actually trying it out), combined the information into a simple table, much as I first felt should be done, this does mean the price brackets can't change per month, while my solution does allow for this. Your choice. Hope this helps, and feel free to ask me for any clarifications of what I've done. Paul P.S. On a separate note, how does one sign macros for use with LO? I've come across the same issue with Excel, and never got round to finding out. If one can sign the macros, can the end user choose which signed macros to trust, or does one essentially have to buy a trusted certificate, and end users only get the option of trusting all such certificates? This is how I understand Excel to work, though I may be wrong. I really feel there should be an easy way to generate self-signed certificates for one's own macros, and end users should be given a white-list of certificates which they can trust, such that you can create macros and sign them, and simply tell your end users to add your certificate to their trust list, and all macros from you will be run without questions, while all other macros will not be run, without the end user having to choose to allow your macros each time, like medium security caters for. Perhaps this should be a feature request? Or am I horribly missing the point of macro security? On Sun, 20 Oct 2013 01:15:05 +0200 Paul paulste...@afrihost.co.za wrote: As I noticed you're practically around the corner from me, I got interested, and had a look at your spreadsheet :) [tl;dr: I got solutions, but need some help uploading the file. Also, it may be that you could work it out in a *much* simpler way, using what you already have, but a) something is broken in your other formulas, and b) that's if they are supposed to do what I think they are.] I can see what you are trying to do, but it took some figuring out. I'm not convinced you have everything laid out correctly in that file. For example, your Max Units column has a complicated calculation that doesn't actually just figure out the difference between the two brackets (as I called the groups). For example, between the 0-150 bracket and the 150-300 bracket, the difference should be 300 - 150, but instead you calculate something using an IF. I don't know why, but I wasn't really focusing on that, it just seems odd (and possibly incorrect) to me. As for your problem at hand. The formula you are using is way too complex. I would not use something like that, I would either split it up into more columns, or write a user function for it. I actually played a little with it to figure out how it worked, and ended up amending your spreadsheet to include both solutions. As I understand the problem, you have a value that needs splitting up into the different price brackets, and then a per bracket price is calculated, and you need the total of the per bracket prices. The split is by amount, that is to say the first bracket is the first 150 units of the value, the second bracket is the next 200 units, and so on. Each bracket has a unit price, and once you figure out how many units of the value falls into each bracket, you can work out the price for those units, and then total it to get a final price for the value. Sorry if that is a convoluted explanation, but it's the best way I could think of to describe it. It is not clear to me how VLOOKUP could be used for this, as the problem as I understand it isn't one of matching your cell to a given list of criteria and inserting a corresponding value. I also can't explain my solutions here, as I haven't simply fixed your formula. I have saved your file with both solutions. I would gladly upload it for you to look at, but I'm not
[libreoffice-users] Re: Feature Request: Lack of Outliner Functionality a Deal Breaker for Me
Hi, Tony, I understand that you're getting great value from the navigator in LO. There are features, however, in Word that are hard to live without for me in what I do all the time. As I described in my original post, I currently drag and drop paragraphs which do not have headers, and I don't like the idea of having to put a header on every paragraph I want to use the process for. In addition, I can use a single click of the button to disappear all but the first line of the text, which can be very long on a wide screen computer with no word wrap. To accomplish the efficiencies of this way of organizing with LO takes much longer and more key strokes and mouse clicks. Someone else made a suggestion that was different than yours, and since I had just used my method, I was able to give an exact comparison between his method and mine. I don't have a similarly short project to do the same with your method, but his method would have taken around 450 mouse clicks to accomplish what I did with 63 clicks and much less time. I apologize if this is comparing apples with oranges, but I also do understand that I'm comparing fruit with fruit, and there is some validity to doing so. No one has suggested anything that comes close to my methods of work. Someone suggested Scrivener, and when I was checking out the site, I discovered their Scapple mind-mapping software, which was so much better than anything that I've tried so far that I immediately got distracted, bought it, and have been using it for a massive project I'm now very focused on. But I never got around to actually checking out Scrivener, so I have no comment on it, other than I'm predisposed to liking their software now. At the moment, I'm mostly overwhelmed with my current massive project, working late into the night, every single night, and I'm not giving much attention to this discussion any more. Even romance has trouble breaking through my focus, right now--and believe me, romance has an upper hand in any battle between her and this discussion. Thanks for your suggestion. -- View this message in context: http://nabble.documentfoundation.org/Feature-Request-Lack-of-Outliner-Functionality-a-Deal-Breaker-for-Me-tp4077564p4078951.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: Feature Request: Lack of Outliner Functionality a Deal Breaker for Me
As a technical writer I understand the need for an Outliner. Well LibreOffice has Outliner built in!!! If you use styles on your paragraphs and then use the Navigator (F5) you can move headings up and down. You can move the heading and all the text below the heading or just the heading. So just create headings of different levels. Works just like I remember Word 2003 working. Haven't used Word since 2003 so am not sure if the outliner features have changed. Try the LibreOffice Navigator and you will be surprised at what can be done. Hope this helps Tony -- View this message in context: http://nabble.documentfoundation.org/Feature-Request-Lack-of-Outliner-Functionality-a-Deal-Breaker-for-Me-tp4077564p4078946.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] Calc IF problem
Le 20/10/2013 21:01, Paul a écrit : On Sun, 20 Oct 2013 18:10:32 +0100 Brian Barker b.m.bar...@btinternet.com wrote: Chacun à son goût, of course. Exactly. The actual expression is: des goûts et des couleurs [ça ne se discute pas] literal EN: Tastes and colours [that's not debatable] :-) -- Jean-Francois Nifenecker, Bordeaux -- 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
