Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenFri, 06 Sep 2019 18:07:08 +0200Weighted Average. Runtime Error: Index out of defined Range.https://ask.libreoffice.org/en/question/207684/weighted-average-runtime-error-index-out-of-defined-range/Function Weighted_Average(optional x(), optional w())
dim WA as double
dim SUMWX as double
dim SUM as double
For i = 1 to UBound(x)
SUMWX = SUMWX + w(i,1)*x(i,1)
Next i
For b = 1 to UBound(x)
SUM = SUM + w(i,1)
Next b
WA = SUMWX/SUM
Weighted_Average = WA
End FunctionJackZedFri, 06 Sep 2019 18:07:08 +0200https://ask.libreoffice.org/en/question/207684/Re-usable spreadsheet (or "when you don't know in advance the number of rows")https://ask.libreoffice.org/en/question/205836/re-usable-spreadsheet-or-when-you-dont-know-in-advance-the-number-of-rows/Hi,
I'm trying to write a spreadsheet that I could re-use easily even when a number of rows change.
One of the cell (say, on row 6) compute the average, of, say, five other cells, using
= AVERAGE(A1:A5)
The thing is that, sometimes, I want to add, say, 10 rows, after the 5th row, because some situation change.
I'd like that cell computing the average to be automatically
1) moved down,
2) updated, so that it would compute the average of A1:A15.
Is there a way to simply do that?
Thanks.ClémentFri, 23 Aug 2019 14:50:03 +0200https://ask.libreoffice.org/en/question/205836/Turkey shoot? Averaging multiple data across multiple sheets, while looking for name that are constantly movinghttps://ask.libreoffice.org/en/question/204563/turkey-shoot-averaging-multiple-data-across-multiple-sheets-while-looking-for-name-that-are-constantly-moving/I'm a complete knob at this, plus permafrost has started to settle in and disrupt the thinking process. So I need all the help I can get.
I'm working on a Calc project that has 12 sheets one for each month of the year. on each sheet are tables containing multiple names and with each name a list of 9 things that vary each month. Each month the names in each table can shift from one table to the next depending on their assignment. I'm looking to be able to collect the data for each name and average it on a 3/6 month basis.
Thanks in advance for any help or direction given[C:\fakepath\test 2019.ods](/upfiles/15658329548793966.ods)davercTue, 13 Aug 2019 23:28:17 +0200https://ask.libreoffice.org/en/question/204563/Incorrect average of time in Calchttps://ask.libreoffice.org/en/question/204492/incorrect-average-of-time-in-calc/ In calc, I have a column showing times of arrival:
8:00:00 AM
7:50:00 AM
7:55:00 AM
7:50:00 AM
8:20:00 AM
8:00:00 AM
8:05:00 AM
8:05:00 AM
8:00:00 AM
At the bottom of the column, I have a column with the calculation: =AVERAGE(B10:B18). The result that it gives me is "10:40:00 AM". Unless I've made a dumb mistake...that's not right. Just by doing some of the math by hand, that should be ~"8:00:00 AM".
All of this is imported from excel, where the formula worked.
Is there another formula that I should be using?alderekTue, 13 Aug 2019 15:42:03 +0200https://ask.libreoffice.org/en/question/204492/Averaging separate numbers in a listhttps://ask.libreoffice.org/en/question/201021/averaging-separate-numbers-in-a-list/How do I tell calc to always show me the average of the last 10 numbers entered from a list containing random blank spaces?
![image description](/upfiles/15630455766331777.jpg) catontopSat, 13 Jul 2019 21:22:14 +0200https://ask.libreoffice.org/en/question/201021/Is this macro's purpose achievable with a regular Calc function?https://ask.libreoffice.org/en/question/191600/is-this-macros-purpose-achievable-with-a-regular-calc-function/Basically I have been scratching my head trying to accomplish the following:
I have a table full of data, and I want to calculate the highest average of all the rows of that table
So for example if I had
1 4 5 7
3 4 2 6
2 8 1 5
6 3 4 1
I would need to calculate the 4 averages, one per row, and then get the highest one.
(So the averages would be 4.25 , 3.75 , 4 , 3.5 ; and it would choose 4.25 for being the highest)
Keep in mind that the table is periodically getting new data so I cant check a fixed set of rows (I can check a large set of rows and just ignore blank rows)
Also I know a simple solution would be to create a column which would hold all the averages and just get the highest one from there, but the idea is not to create more tables because I need to do way more operations than the average I used as an example here.
Since I couldn't come up with a solution, I looked into macros and got this (all the variables and declarations are excluded from the paste):
Value = -1000
i = 0
data = range.GetDataArray() // range is a Cell range
while data(i)(0) <> ""
CurrValue = svc.callFunction("Average",data(i)) // svc is createUnoService("com.sun.star.sheet.FunctionAccess")
if Value < CurrValue Then
Value = CurrValue
End if
i = i + 1
WEnd
AFX = ThisComponent.Sheets(0).getCellRangeByName("AF1")
AFX.Value = Value
This works, but I want to know if this result would be achievable with built-in Calc functions.
Thanks.PumFri, 26 Apr 2019 07:18:39 +0200https://ask.libreoffice.org/en/question/191600/use product of formula in another formulahttps://ask.libreoffice.org/en/question/187049/use-product-of-formula-in-another-formula/ i need to get product of multilpe formulas and get averagea2drianSat, 16 Mar 2019 11:55:07 +0100https://ask.libreoffice.org/en/question/187049/[SOLVED] Only average if cells contain a certain wordhttps://ask.libreoffice.org/en/question/185512/solved-only-average-if-cells-contain-a-certain-word/ Hello!
In this example I'd like F7 to calculate the average time of the range D5:D29 only if the cell left to it contains the word "Monday" i. e. C7 shall average all times on mondays.
How do I go about solving this?
![image description](/upfiles/15517744112668775.png)LaniakeaTue, 05 Mar 2019 09:31:57 +0100https://ask.libreoffice.org/en/question/185512/Average row with multiple numbers (0 1 3 5 6 1 2 3 5 4 0 )https://ask.libreoffice.org/en/question/178796/average-row-with-multiple-numbers-0-1-3-5-6-1-2-3-5-4-0/ A | B | C | D | E
1 (1 2 3) | (3 3 3) | | |
2 (3 3 3) | (1 2 3) | | |
3 (4 4 4) | (4 4 4) | | |
...
If I make average **=AVERAGE(A1)** it doesn't count every number in the row to sum.
Separating every number in different rows and then doing some hack to average them makes the whole sheet messed up.
I want simple average A1 but counting all numbers separated by spaces.andradadaWed, 09 Jan 2019 22:21:19 +0100https://ask.libreoffice.org/en/question/178796/Average time between actionshttps://ask.libreoffice.org/en/question/175184/average-time-between-actions/ I have a column that has a date and time recorded in it like 09/19/18, 9:34 AM. I would like to find the average time between these recordings each day, then average all the days together. So if I have recordings at 9:39, 2:10, 5:30, 7:30 etc, what is the best way to format the cells to achieve this?WhitelionessWed, 05 Dec 2018 18:34:55 +0100https://ask.libreoffice.org/en/question/175184/¿Object variable not set?https://ask.libreoffice.org/en/question/82713/object-variable-not-set/I have this error:
BASIC runtime error.
Object variable not set
And this is the code:
REM ***** BASIC *****
Private Sub Average()
dim Total_Marks as Integer
Total_Marks = Application.WorksheetFunction.Average(Sheet1.Range("G" & 31 & ":G" & 42))
ThisWorkbook.Worksheets("Sheet1").Cells(8,45).Value = Average_Marks
End SubecarvalloTue, 29 Nov 2016 01:48:39 +0100https://ask.libreoffice.org/en/question/82713/Average for multiple entrieshttps://ask.libreoffice.org/en/question/162152/average-for-multiple-entries/In column A I have the freelancers' names, in column B their ratings. These are ratings for each task each freelancer has performed. For example John has performed 3 tasks so far - one task got a rating of 5, the other a rating of 2, and the third a rating of 1, for an avg. of 2.66. And since there are constantly new tasks being performed the averages are constantly changing.
I'd like to get the average rating for each freelancer.
John 5
Jane 3
Tim 5
Sarah 1
John 2
Jane 5
Tim 3
Sarah 2
John 1
Jane 2
Tim 1
Sarah 4
Averages:
Tim: Sum 9 - Avg 3
Sarah: Sum 7 - Avg 2.33
John: Sum 8 - Avg 2.66
Jane: Sum 10 - Avg 3.33kd93Mon, 06 Aug 2018 17:04:13 +0200https://ask.libreoffice.org/en/question/162152/Future Date and Lookback average movehttps://ask.libreoffice.org/en/question/135456/future-date-and-lookback-average-move/ Please see file
I would like to calc average price movement based upon number of days from Future date to current date column D, look back average price movement (column C) based upon how many days from Future date -current date in column D.
example:
if it show 7 days in column D, then it will show average price movement past 7 days from Column C (c14:c20).
If column D shows 14, it will look back from that Date, 14 days average price (c19:c32)
thank you in advance for anyone providing help
[C:\fakepath\Future Date and Average move.ods](/upfiles/15086744138503304.ods)JG101Sun, 22 Oct 2017 14:15:04 +0200https://ask.libreoffice.org/en/question/135456/referencing values across multiple fileshttps://ask.libreoffice.org/en/question/116297/referencing-values-across-multiple-files/ I have a template document (.ots) with a macro that exports and saves the new file as .ods to a desired location. What i want to do is create a new document that gives me the average values across all the exported files, given that the number of files it'll need to reference will increase as i use the template document more often so there will be more exported files over time. Is there any way to do this, like "average all the values in this given range on all the .ods documents in this folder"sleeperstakesSun, 30 Jul 2017 16:56:43 +0200https://ask.libreoffice.org/en/question/116297/Averages not calculating correctly on Calc?https://ask.libreoffice.org/en/question/98067/averages-not-calculating-correctly-on-calc/ so I'm doing a poll sort of thing and the averages aren't coming out correctly. people are rating the songs out of ten, and when I averaged out their votes, the average came out incorrectly:
![image description](http://i.imgur.com/nDJ8sYD.png)
if you're wondering, the pixelated parts in B, C, and D are people's names, formatted in bold.
8+3+10 is 21, and 21 divided by three is 7, not 9 which is what the AVERAGE function is displaying. I have no idea if it's A1 being blank or E1 being blank… or what… I need help. I don't know what I'm doing wrong.ybbySat, 03 Jun 2017 23:35:03 +0200https://ask.libreoffice.org/en/question/98067/average of sumshttps://ask.libreoffice.org/en/question/82687/average-of-sums/Let's say I have some data on columns B through E that I want to average as follows:
- for each row: sum the four columns
- then take the average such sum
Do I *have* to get another column for the sum and then take the average of that column or is there a more direct approach? Because pulling ``=SUM(...)`` down ``500'000`` rows **does** take a while. DiesNutsMon, 28 Nov 2016 16:54:11 +0100https://ask.libreoffice.org/en/question/82687/average over merged cellshttps://ask.libreoffice.org/en/question/77684/average-over-merged-cells/ Hi,
I have 22 cells merged into 11 cells. So 1 merged cell is basically 2 individual cells. When i check the average over the cells, the result is basically half. I presume that its counting all the 22 cells instead of 11. Is there any particular way this should be done, or is it a known issue.
achopraTue, 20 Sep 2016 20:41:08 +0200https://ask.libreoffice.org/en/question/77684/How can I use a sheet name in formulas? SHEET doesn't work!https://ask.libreoffice.org/en/question/77680/how-can-i-use-a-sheet-name-in-formulas-sheet-doesnt-work/Hi,
I am dumping data into sheets and naming them with dates, I want to have a master sheet that pulls info from lots of various sheets but I only want to type the sheet name once, not 25 times inserting it into formulas, by typing the sheet name into one cell and a formula referencing it, but it doesn't seem to work.
here is an example.
this works
=AVERAGE('20-07-16'.C1383:C1442)
but I want to use some thing like
=AVERAGE(A32.C1383:C1442)
where A32 is a sheet name?
Any thoughts on why this won't work?
Many thanks
JimJimtechTue, 20 Sep 2016 19:30:39 +0200https://ask.libreoffice.org/en/question/77680/How Do I average rows?https://ask.libreoffice.org/en/question/48752/how-do-i-average-rows/It will let me average columns but not rows, can I average rows? For example I am trying to average e4 through n4, but instead of getting the average it only gives me ###. The formula I am uses in =average(e4,n4)LinkMEPSat, 04 Apr 2015 19:19:10 +0200https://ask.libreoffice.org/en/question/48752/How can non-contiguous entries be selected for use with the AVERAGE function?https://ask.libreoffice.org/en/question/38329/how-can-non-contiguous-entries-be-selected-for-use-with-the-average-function/HOW are they selected?abiThu, 14 Aug 2014 20:43:07 +0200https://ask.libreoffice.org/en/question/38329/Complicated Calc spreadsheet, how to classify a list by the "best"?https://ask.libreoffice.org/en/question/33227/complicated-calc-spreadsheet-how-to-classify-a-list-by-the-best/I'd rather explain what I need to do with an example instead of using words. Talking about LibreOffice Calc here.
Let's say that I have the detailed characteristics of 3 racing cars and want to sort/classify them from the best to the worst.
(downloadable example Calc file here: http://en.libreofficeforum.org/sites/libreofficeforum.org/files/uploads/WAZAAAAA_files/StupidExample.ods)
Ferrari - MAX SPEED:200km/h - ACCELERATION SPEED:8sec - MASS:1100kg
McLaren - MAX SPEED:110km/h - ACCELERATION SPEED:10sec - MASS:1090kg
Honda - MAX SPEED:120km/h - ACCELERATION SPEED:10sec - MASS:1091kg
The calculation should take in consideration that higher=better for MAX SPEED, and lower=better for ACCEL SPEED and MASS. It should also consider that the MASS advantage for the Honda over the Ferrari is a very small "victory" (+0,82%) compared to the huge difference in MAX SPEED (-66%).
Naturally, the results from the best to the worst in the example would be Ferrari 1st, Honda 2nd, McLaren 3rd, but how do I process all of this with Calc? And HOW MUCH (maybe expressed in percentage) is each car better than the other?
Thanks in advance.WAZAAAAASun, 27 Apr 2014 20:07:19 +0200https://ask.libreoffice.org/en/question/33227/How do I write a frequency function?https://ask.libreoffice.org/en/question/30783/how-do-i-write-a-frequency-function/Version: 4.1.3.2, English, Calc
Ubuntu 13.10, English
Hello,
I am working on some science fair stuff and I have respiration data that was taken by a relative pressure sensor that was connected to a bladder that was strapped around the test subjects’ stomachs. As they inhaled, they created pressure against the bladder raising the pressure readout and vice versa, which created a somewhat harmonic wave (I’ve attached a graphic). I need to write a function that will calculate the average frequency of troughs or breaths. Something that could perhaps take the frequency of two or so troughs in front and behind the one in question and average them to give a breath frequency. Does anyone know how to write such a thing?
Someone on another forum said this:
"I would approach this by creating a new pair of data columns that indicate each peak and trough point using basic less-than / greater-than comparisons of a prior value (or perhaps 3 prior readings for noise smoothing purposes). It should then be possible to determine each period and display these in another column. These periods can then be averaged as required in the moving 5-period manner indicated (again in a new column) and graphed as required."
I didn't understand that. I've attached a file of some sample data. [C:\fakepath\Data Sample.ods](/upfiles/13941180328547709.ods) Feel free to edit, alter and otherwise play with it if it helps. It will certainly help me if you can return a file that has an example of what you're saying.
Thanks!burnst14Thu, 06 Mar 2014 16:05:20 +0100https://ask.libreoffice.org/en/question/30783/how do I add an average button in calc ?https://ask.libreoffice.org/en/question/21848/how-do-i-add-an-average-button-in-calc/I cannot see how I can add an "average" button in Calc. I can see the "sum" button, but would like an average button also. Is this possible ?pangitMon, 26 Aug 2013 12:22:32 +0200https://ask.libreoffice.org/en/question/21848/display average and sum for highlighted cellshttps://ask.libreoffice.org/en/question/6785/display-average-and-sum-for-highlighted-cells/In excel if I highlight a series of cells, the average for the values and the sum is automatically displayed in the bottom status bar.
Is this possible to do in librecalc?
Thanks!TheChucksterFri, 12 Oct 2012 04:55:56 +0200https://ask.libreoffice.org/en/question/6785/