You can try the steps below. I have modified your formula a little bit. The OFFSET function is performed first; allowing to shift the cells based on their column. Then, the average is calcualted.
- On A1, keep the average formula:
=AVERAGE(A1:B1)
- On B1, enter the following formula on the next cell:
=AVERAGE(OFFSET(A1:B1,0,COLUMN()))
- Drag B1 to the left to fill the other cells
Note: You need to modify the formula to take into account when there are empty cells as you will get #DIV/0 or other errors.
Hello!
I've been trying to get data from multiple rows that contain the same input. Something like this:
| Item | Number of items |
|---|---|
| A | 2 |
| B | 1 |
| C | 3 |
| A | 5 |
| D | 2 |
And from this table I would like to find how many items A are there. The solution I came up with in excel is:
=SUM(IF(OFFSET(A1;0;0;5;1)="A";(OFFSET(A1;0;0;5;1)="A")*OFFSET(A1;0;1;5;1);0)
The first OFFSET makes an array {1;0;0;1;0}. In the second part I multiply that array with the second column ("Number of items") which gives {2;0;0;5;0}, and then I just SUM the last array. This works perfectly in excel, but in Libre Office Calc it doesn't work because I think that OFFSET doesn't produce an array of results like excel. What can I do to get an equivalent result in Calc?
Any help is appreciated!
Before asking I searched for answers. Details at the end.
=======
Setting b13 =OFFSET(i54,4,0) works. It shows the value of i58.
Setting b13 =OFFSET(INDIRECT("B12",4,0) fails. B12 is set =i54, so b13 should show the value of i58, but it shows the value of b16.
How can I get b13 to show the value of i58? (There are dozens of similar operations required, so I'm hoping to find a formula that calculates everything with one edit, rather than needing to edit each cell individually. (The cells aren't contiguous so I can't just copy and paste a row/range.) )
Setting b13 =INDIRECT("B12") seems to work, but the function seems meaningless if it's the only function. It shows the same result as if b13 was set =B12.(I don't understand why quotes are required, but they are. Without them, you get a #REF error.)
=======
File type: ODS
Version: 7.0.1.2 (x64)
Build ID: 7cbcfc562f6eb6708b5ff7d7397325de9e764452
CPU threads: 8; OS: Windows 10.0 Build 18363; UI render: Skia/Raster; VCL: win
Locale: en-CA (en_CA); UI: en-US
Calc: threaded
======
I searched Libreofficehelp.com, but it seems to not mention INDIRECT, even though INDIRECT is a formula in LibreOffice. I also googled, but found only mentions of the difference between Excel (uses an exclamation mark to denote a sheet name) vs LibreOffice (uses a period). I'm searching within the same sheet, so those results were irrelevant.
Hello, I'm trying to put together what I'm sure is a fairly simple formula and I know its some combination of Index, Match, and Offset, but I can't seem to wrap my peasized brain around it after trying for two days. I'm trying to look up a specified value in a column and return the value x rows down from that specified value. So my components are:
Scale: Lets call it A1:A10, this is the column with multiple values
CurrentValue: This is the specified value I need to reference in the Scale
Adder: This is the number of rows down from CurrentValue that I need to pull the value from
I don't suppose someone can point me to the right formula? Thanks
If you're asking for help with LibreOffice, please make sure your post includes lots of information that could be relevant, such as:
-
Full LibreOffice information from Help > About LibreOffice (it has a copy button).
-
Format of the document (.odt, .docx, .xlsx, ...).
-
A link to the document itself, or part of it, if you can share it.
-
Anything else that may be relevant.
(You can edit your post or put it in a comment.)
This information helps others to help you.
Important: If your post doesn't have enough info, it will eventually be removed, to stop this subreddit from filling with posts that can't be answered.
Thank you :-)
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.
As far as I know you cannot apply an offset to the result of a look-up, which seems to be what you're after.
I'd tackle this by introducing a new column to hold the offset values. Something like this (formula view). Enter the value to lookup in E2 and the row offset in D2. The offset value is in F2
You really should include some kind of mockup that displays what you want to achieve. It makes it so much easier to give you useful answers.
LibreOffice Calc is always super slow.
Spreadsheet/file is almost empty. No formulas, just a few texts.
Clicking from a cell to another and other actions takes like half a second (which is extremely slow for a software UI).
Using LibreOffice 25.2.0.3 , OS is Windows 11 Pro.
I have not used other LibreOffice software recently so I don't know it it's only Calc.
Is this a know issue, can it be fixed ?
EDIT: So, solution found:
In Calc:
-
I went to Tools > Options > View
-
Unchecked "Use Skia for all rendering"
-
(Already felt improvement)
-
Unchecked "Use hardware acceleration"
-
(Possibly felt further improvement)
-
Calc feels more or less back to normal.
