moving average

[nhoe1957]

Hi
I'm new here

Thanks for the help

I know how to do the normal moving average.

But I am interested in only having the last moving average shown.

example below, I want let's say the last 5 day's MA to display in the XXXXX
and as I add data, 7-20, 7-23, 7-24, etc. that the MA XXXXX automatically updates in that cell ONLY. I am not interested in viewing the past MAs. Only for the most current data.

MOVING AVERAGE
XXXXX

data set

date $

7-10 100
7-11 20
7-12 50
7-13 -20
7-14 250
7-17 -100
7-18 50
7-19 65

 

[nhoe1957]

Thanks Matty
scottylad2
AlphaFrog

All work like champs
More than one way to 'skin a cat'

 

[nhoe1957]

and thanks also

bosco_yip

 

[nhoe1957]

okay now is this possible.

I'm using Matty's formula

=AVERAGE(OFFSET(B1,MATCH(9.99E+307,B:B),0,-6,1))


new situation.

Is it possible to have a 5 day moving average calculate the LAST 5 numbers = inputs?

This is because not everyday there is a number or value.
example would be

date Value 1 Value 2 Value 3
5-10 .....10.... 5........ 2
5-11 .....9................. 3
5-12.... 8........ 6....... 2
5-13 .....12...... 5
5-14 ...............9...... 4
5-15 ...11................. 3
5-18 ....9........ 5....... 5

5dayMA 9.80..... 6.0...... 3.4

currently with this formula, if I am missing a day or value, the 5 day MA is an invalid number.

thank you in advance.

 

[AlphaFrog]

Excel Workbook
	A	B	C	D
1	Date	Value 1	Value 2	Value 3
2	10-May	10	5	2
3	11-May	9		3
4	12-May	8	6	2
5	13-May	12	5
6	14-May		9	4
7	15-May	11		3
8	18-May	9	5	5
9
10	5dayMA	9.8	6.0	3.4
...

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.<br>

 

[nhoe1957]

so if I am adding data everyday, i.e. May 19, 20, 21, etc.

the formula should be =

={SUM(IF(ROW(B:B)>=LARGE(IF(B:B>0,ROW(B:B),""),5),B:B))/5}

thank you

 

[AlphaFrog]

You would have to use a defined range. It wouldn't work with referencing the entire column. The defined range can be larger than your current data to accommodate additional future entries; e.g.
=SUM(IF(ROW(B2:B100)>=LARGE(IF(B2:B100>0,ROW(B2:B100),""),5),B2:B100))/5

This formula can't be within B2:B100

 

[nhoe1957]

Thank you!

 

[nhoe1957]

Hi AlphaFrog

question, does this work if the data or numbers are negative?

I tried it but when I enter negative numbers, or losses, it didn't work.
Am I doing something wrong?

 

[AlphaFrog]

Try something like this...

=SUM(IF(ROW(B2:B8)>=LARGE(IF(B2:B8<>"",ROW(B2:B8),""),5),B2:B8))/5

 

[nhoe1957]

PERFECT!

MANY Thanks!