[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
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
Re: [libreoffice-users] Calc: help with VLOOKUP
How do you name an array? On February 15, 2014 11:37:54 AM EST, Carl Paulsen carlpaul...@comcast.net 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] 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
[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] 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
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
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
