most recent average value

Column B values are the average of the most recent five values in column A.
The formula in Cell C1 is “=B7” so that the value in C1 is 1.17.
My question is: When I place a value in cell A8, which will result in the average of the five numbers in column A4-A8 being placed in cell B8 how do I make the value in C1 change to be the value which is in cell B8 rather than the value which is in cell B7?
Note: I just want to be able to pick up the most recent average value – the
method whereby it is done is of no consequence. However I cannot think
of an alternative.
A B C
1 1.17
2 2.12
3 1.02
4 1.26
5 0.84
6 1.33 1.31 1.31=average of the five numbers in column A2-A6
7 1.39 1.17 1.17=average of the five numbers in column A3-A7
8 --- ---

Try:

=Lookup(9.99999999e+307,B:B)

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save