Average to date

English
#1

I have a number of rows with two columns, each containing a date and a number. Example:

Average to date

In column C, I’d like to display an “average to date” of the numbers. To hopefully be clear:

  • C3 to contain the average of B2 through B3.
  • C4 to contain the average of B2 through B4.
  • C5 to contain the average of B2 through B5.
  • C6 to contain the average of B2 through B6.
  • Etc…

Can’t seem to figure this one out. Any suggestions on the proper function (or combination of functions) I should be using?

Thanks in advance!

#2

Type/paste in C3: =AVERAGE(B$2:B3), then fill down.¹

EDIT:
¹ Copy and paste; OR
Drag down the fill square (the black box at the lower right corner of the selection); OR
Double click in the fill square (will work as long as column A has its cells occupied).

1 Like
#3

Perfect Leroy, thanks very much!

I did try using AVERAGE with combinations of relative and absolute cell values then copying/pasting to the cells below, but for some reason couldn’t get it to work. Seems dragging to fill them is what’s required to do the trick.

1 Like
#4

Try and error could be a bad approach. When you drag down Leroy’s formula, do you see how it works? The end row is relative but not the anchor to the start row $2.
$B$2:$B3 would work as well.

No. It’s just another way to copy the formulas.

#5

Yes, I believe that’s actually called a “mixed reference”, and one I wasn’t familiar with.

Please correct me if I’m wrong:

  • $B$2 (or B$2) locks the first cell.
  • $B3 (or B3) will automatically vary by row when the formula is copy/pasted.

Dragging was another copy method I wasn’t familiar with, and much faster than the usual route.

So, a couple of things I picked up here, great!

#6

Locks the row and the column of the first cell. It remains $B$2, no matter if you copy vertically or horizontally.
B$2 remains B$2 when you copy vertically but becomes C$2, D$2, E$2 … when you copy to the right because the column is not frozen.
So in this particular case $B$2 or B$2 makes no difference as long as you copy downwards.

1 Like
#7

Third thing. :slightly_smiling_face:
Suppose you want to fill a formula from C3 to the range of cells C3 through C50000.

  • Type C3:C50000 in the name box and press Enter. The range of cells must be selected.
  • Enter Ctrl + D.
1 Like
#8

3 minutes excercise on a blank sheet:
Click a cell in the middle of the screen, say G20.
Enter a reference to some cell above/left of G20, say E17.
Copy or drag down G20.
Copy or drag G20 to the right side.
Copy or drag G20 up until row 1.
Copy or drag G20 to the left until column A.

What can you tell about the #REF error?

Do the same with G20 =$E17 and =E$17

#9

https://help.libreoffice.org/latest/en-US/text/scalc/guide/relativ_absolut_ref.html

https://help.libreoffice.org/latest/en-US/text/scalc/01/04060109.html#hd_id3146968

#10

And another, which covers all three:

https://www.libreofficehelp.com/absolute-relative-mixed-reference/

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.