Re: [libreoffice-users] Calc: help with VLOOKUP
Le 15/02/2014 22:06, Jean-Francois Nifenecker a écrit : > Le 15/02/2014 21:14, Pikov Andropov a écrit : >> >> Data > Define Range >> > > There, you define a dataset which can be further managed for data > retrieval, sorting, filtering, mailing... > The column headers are seen as field names and the other rows as records. -- 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
Re: [libreoffice-users] Calc: help with VLOOKUP
Le 15/02/2014 21:14, Pikov Andropov a écrit : > > Data > Define Range > There, you define a dataset which can be further managed for data retrieval, sorting, filtering, mailing... -- 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
Re: [libreoffice-users] Calc: help with VLOOKUP
Jean-Francois Nifenecker has written on 2/15/2014 12:46 PM: > Le 15/02/2014 18:20, Pikov Andropov a écrit : >> How do you name an array? >> > > (1) Select the array > > (2) Menu Insert > Names > Define > > (3) Enter the name of your choice (no spaces allowed) and hit OK. > > From now on, using the array in a formula is equivalent to the absolute > reference to this array. > > EG: you select A1:B2 and name it "my_array" > You may write formulae using the $A$1:$B$2 or the my_array syntax (note > that you must not use quotes when dealing with array names in formulae). > That is: > =SUM($A$1:$B$2) > or > =SUM(my_array) > give the same result > > HTH, > Merci, Jean-Francois! What's the difference between this kind of named range and the one you get by doing Data > Define Range in step 2? -- 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: Possible Bug in 4.2.0.4 Copy Paste in Firefox ESR 24.3ESR
I confirm that can not be copy from LibreOffice and pasted on gmail. When I use notepad as an intermediary, I can copy form notepad and paste in gmail. -- 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] averaging with missing values
Hi Regina, Le 15/02/2014 17:49, Regina Henschel a écrit : > > If the cells are really empty or have a function call that results in a > text, then such cells will not be counted by the AVERAGE function. If > the cells contain a formula, which results in 0, then you can use > AVERAGEIF(range;"<>0"). > Thanks for the pointer, but... well, I didn't find any MOYENNE.SI() (or equivalent) in my FR version 4.1.4.2 Statistics functions. Thus my suggestion to simulate one :,( Hu? Yes, there is, but classified in the Math functions, while AVERAGE() [MOYENNE() in FR] is classified in Statistics :,( Thanks for the heads up. -- 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
Re: [libreoffice-users] Calc: help with VLOOKUP
Le 15/02/2014 18:20, Pikov Andropov a écrit : > How do you name an array? > (1) Select the array (2) Menu Insert > Names > Define (3) Enter the name of your choice (no spaces allowed) and hit OK. From now on, using the array in a formula is equivalent to the absolute reference to this array. EG: you select A1:B2 and name it "my_array" You may write formulae using the $A$1:$B$2 or the my_array syntax (note that you must not use quotes when dealing with array names in formulae). That is: =SUM($A$1:$B$2) or =SUM(my_array) give the same result HTH, -- 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
Re: [libreoffice-users] Calc: help with VLOOKUP
How do you name an array? On February 15, 2014 11:37:54 AM EST, Carl Paulsen wrote: >Jean-Francois, > >+1 >Thanks for this idea, below. I'd never thought of naming an array, and > >you are right, it would seem to make both generating AND understanding >formulae easier. Plus there's less room for error. In my case I >frequently use the same array for a variety of vlookups, so the added >step of naming could end up saving me some time as well. > >Carl > > >On 2/15/14 2:55 AM, Jean-Francois Nifenecker wrote: >> Le 14/02/2014 23:06, Carl Paulsen a écrit : >>> A few hints for vlookups: I find I usually want to add "hard" >>> references for the array so that it always looks at the intended >rows of >>> the array. To do that, you need to add $ before each cell reference >in >>> the array. So if the array of the function is A1:C100, I enter it >as >>> $A$1:$C$100. This forces vlookup to always look at those cells >>> regardless of what row the calculation is happening on. >> Another way of achieving that is to name the array. This has my >> preference because a named array makes formulae easier to understand. >> >> > >-- > >Carl Paulsen > >Dover, NH 03820 > > >-- >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 -- Sent from my Android phone with K-9 Mail. Please excuse my brevity. -- 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] averaging with missing values
Hi William, william drescher schrieb: I have a table of systolic bloop pressures: 4 rows (weeks) x 21 cols (days) eg: week 1 130 133 140 ... (representing morning, noon, night of day 1 - followed by morning, noon and night of day 2..7) I want to compute the average pressure for the week. In a perfect world I would just use the average function. However, in this world I find that each row has between 3 and 10 missing values. To get the average now I sum the row's values and then divide by the number of values in the row. Can anyone suggest a way of getting the average without needing to manually count the values ? If the cells are really empty or have a function call that results in a text, then such cells will not be counted by the AVERAGE function. If the cells contain a formula, which results in 0, then you can use AVERAGEIF(range;"<>0"). Kind regards Regina -- 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: help with VLOOKUP
Jean-Francois, +1 Thanks for this idea, below. I'd never thought of naming an array, and you are right, it would seem to make both generating AND understanding formulae easier. Plus there's less room for error. In my case I frequently use the same array for a variety of vlookups, so the added step of naming could end up saving me some time as well. Carl On 2/15/14 2:55 AM, Jean-Francois Nifenecker wrote: Le 14/02/2014 23:06, Carl Paulsen a écrit : A few hints for vlookups: I find I usually want to add "hard" references for the array so that it always looks at the intended rows of the array. To do that, you need to add $ before each cell reference in the array. So if the array of the function is A1:C100, I enter it as $A$1:$C$100. This forces vlookup to always look at those cells regardless of what row the calculation is happening on. Another way of achieving that is to name the array. This has my preference because a named array makes formulae easier to understand. -- Carl Paulsen Dover, NH 03820 -- 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] averaging with missing values
Le 15/02/2014 16:59, william drescher a écrit : > > Can anyone suggest a way of getting the average without needing to > manually count the values ? > Create your own "averageif()" function with a formula: SUMIF() / COUNTIF() -- 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
[libreoffice-users] averaging with missing values
I have a table of systolic bloop pressures: 4 rows (weeks) x 21 cols (days) eg: week 1 130 133 140 ... (representing morning, noon, night of day 1 - followed by morning, noon and night of day 2..7) I want to compute the average pressure for the week. In a perfect world I would just use the average function. However, in this world I find that each row has between 3 and 10 missing values. To get the average now I sum the row's values and then divide by the number of values in the row. Can anyone suggest a way of getting the average without needing to manually count the values ? Thanks, -bill -- 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] Mail Merge Wizard fails in 4.2.0
Has anyone else had a problem with the MailMerge Wizard in 4.2.0? In release 4.1.4 and all previous releases it produced the merged data. In release 4.2.0 it gives every indication that it is doing the business by stepping, the correct number of times, through the records in the selected address list. It produces the correct number of pages. Each page has the address block in it. However, each address block is empty. I've tested this on Slackware 14.1 and Windows XP. Has anyone else seen this. If so do you know if a bug has been created for this. I've searched bugzilla and not found anything. 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
