Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenSat, 18 Mar 2017 02:21:51 +0100Assume data in column A repeats the row number. Column B contains random data. What formula (in column C beyond row 10) would yield the average of the last 10 values in column B?https://ask.libreoffice.org/en/question/90462/assume-data-in-column-a-repeats-the-row-number-column-b-contains-random-data-what-formula-in-column-c-beyond-row-10-would-yield-the-average-of-the/ Spread sheet
Fri, 17 Mar 2017 20:55:39 +0100https://ask.libreoffice.org/en/question/90462/assume-data-in-column-a-repeats-the-row-number-column-b-contains-random-data-what-formula-in-column-c-beyond-row-10-would-yield-the-average-of-the/Answer by LKeithJordan for <p>Spread sheet</p>
https://ask.libreoffice.org/en/question/90462/assume-data-in-column-a-repeats-the-row-number-column-b-contains-random-data-what-formula-in-column-c-beyond-row-10-would-yield-the-average-of-the/?answer=90474#post-id-90474I'm taking this off the top of my head while tapping on a cell phone, so my answer may or may not be precisely correct -- but it should give you the right idea. Here is one solution:
Assume your cursor is in cell C20. The last data is in cell B19. Here is a formula:
=AVERAGEIFS($B:$B, $A:$A, ">=$A10", $A:$A, "<=$A19")
By using the combination of relative and absolute cell address referencing, the formula should self-adjust if you copy it further down column C for additional averages.
***Please click the check mark next to the response you believe best answers your question.***Sat, 18 Mar 2017 00:50:58 +0100https://ask.libreoffice.org/en/question/90462/assume-data-in-column-a-repeats-the-row-number-column-b-contains-random-data-what-formula-in-column-c-beyond-row-10-would-yield-the-average-of-the/?answer=90474#post-id-90474Comment by LKeithJordan for <p>I'm taking this off the top of my head while tapping on a cell phone, so my answer may or may not be precisely correct -- but it should give you the right idea. Here is one solution:</p>
<p>Assume your cursor is in cell C20. The last data is in cell B19. Here is a formula:</p>
<p>=AVERAGEIFS($B:$B, $A:$A, ">=$A10", $A:$A, "<=$A19") </p>
<p>By using the combination of relative and absolute cell address referencing, the formula should self-adjust if you copy it further down column C for additional averages.</p>
<p><strong><em>Please click the check mark next to the response you believe best answers your question.</em></strong></p>
https://ask.libreoffice.org/en/question/90462/assume-data-in-column-a-repeats-the-row-number-column-b-contains-random-data-what-formula-in-column-c-beyond-row-10-would-yield-the-average-of-the/?comment=90478#post-id-90478I interpreted your question as something to be re-used -- not as a one shot deal. Apparently @aceking370 read your question correctly.
I suggest you give him a check mark to thank him. Good luck with your spreadsheet.Sat, 18 Mar 2017 02:21:51 +0100https://ask.libreoffice.org/en/question/90462/assume-data-in-column-a-repeats-the-row-number-column-b-contains-random-data-what-formula-in-column-c-beyond-row-10-would-yield-the-average-of-the/?comment=90478#post-id-90478Comment by karolus for <p>I'm taking this off the top of my head while tapping on a cell phone, so my answer may or may not be precisely correct -- but it should give you the right idea. Here is one solution:</p>
<p>Assume your cursor is in cell C20. The last data is in cell B19. Here is a formula:</p>
<p>=AVERAGEIFS($B:$B, $A:$A, ">=$A10", $A:$A, "<=$A19") </p>
<p>By using the combination of relative and absolute cell address referencing, the formula should self-adjust if you copy it further down column C for additional averages.</p>
<p><strong><em>Please click the check mark next to the response you believe best answers your question.</em></strong></p>
https://ask.libreoffice.org/en/question/90462/assume-data-in-column-a-repeats-the-row-number-column-b-contains-random-data-what-formula-in-column-c-beyond-row-10-would-yield-the-average-of-the/?comment=90476#post-id-90476why simple if it could be done sophisticated !?Sat, 18 Mar 2017 01:03:19 +0100https://ask.libreoffice.org/en/question/90462/assume-data-in-column-a-repeats-the-row-number-column-b-contains-random-data-what-formula-in-column-c-beyond-row-10-would-yield-the-average-of-the/?comment=90476#post-id-90476Answer by Mike Kaganski for <p>Spread sheet</p>
https://ask.libreoffice.org/en/question/90462/assume-data-in-column-a-repeats-the-row-number-column-b-contains-random-data-what-formula-in-column-c-beyond-row-10-would-yield-the-average-of-the/?answer=90467#post-id-90467Put this formula into `C10`: `=AVERAGE(B1:B10)`, and drag-copy it downwards as required.
Your column A isn't required for the task.Fri, 17 Mar 2017 21:19:07 +0100https://ask.libreoffice.org/en/question/90462/assume-data-in-column-a-repeats-the-row-number-column-b-contains-random-data-what-formula-in-column-c-beyond-row-10-would-yield-the-average-of-the/?answer=90467#post-id-90467Comment by aceking370 for <p>Put this formula into <code>C10</code>: <code>=AVERAGE(B1:B10)</code>, and drag-copy it downwards as required.</p>
<p>Your column A isn't required for the task.</p>
https://ask.libreoffice.org/en/question/90462/assume-data-in-column-a-repeats-the-row-number-column-b-contains-random-data-what-formula-in-column-c-beyond-row-10-would-yield-the-average-of-the/?comment=90473#post-id-90473Thanks a ton. Simple and I was trying to make something complicated of it.Sat, 18 Mar 2017 00:28:45 +0100https://ask.libreoffice.org/en/question/90462/assume-data-in-column-a-repeats-the-row-number-column-b-contains-random-data-what-formula-in-column-c-beyond-row-10-would-yield-the-average-of-the/?comment=90473#post-id-90473