# Getting 50 months exponential moving average from Data

#### syp1677

##### Member
Dear Gurus,
I have data of stock, attached along with this query.
Column A contains dates and Column B contains closing price at date. I want to derive 50 Month exponential moving average using array function. Data given is daily basis, hence it should be processed monthly, where its corresponding price is average of all price in that particular month. From this I want to calculate 50 months exponential moving average. Please give formulae for Google sheet also.

Thanks in advance.

#### Attachments

• Share_data.xlsx
151.2 KB · Views: 1
Last edited:

#### syp1677​

Have You tested below?

#### syp1677​

Have You tested below?
Thank you for response. I did that , please see attached file. I was interested in having array function to get recent 50MEMA.
If you can suggest any..

#### Attachments

• Share_data.xlsx
277.1 KB · Views: 4
See formulae in cells N2:L2 of the attached.

#### Attachments

• Chandoo56538Share_data2.xlsx
294 KB · Views: 4
See formulae in cells N2:L2 of the attached.
Thank you p45cal, I am using Microsoft office home and student 2021. I dont see EMA function in it.

I dont see EMA function in it
No, it's only in the workbook I shared with you. It should work in your version. To check, adjust the value in cell Q1 and see if the values in column L change.
An easy way to transfer the function to your own workbook is to copy the single sheet in the workbook I shared to your workbook, then immediately delete that sheet again in your workbook. The function should stay.

No, it's only in the workbook I shared with you. It should work in your version. To check, adjust the value in cell Q1 and see if the values in column L change.
An easy way to transfer the function to your own workbook is to copy the single sheet in the workbook I shared to your workbook, then immediately delete that sheet again in your workbook. The function should stay.
I am sorry but I am not getting when you say transfer the function.

1. In the book I attached, if you change the value in cell Q1, do the EMA values update?
2. If they do, copy that sheet to your own workbook:
right-click the sheet concerned's tab and choose:

Switch to that workbook and check again that the formula still works by changing cell Q1.
Then introduce the function on your own sheet ensuring it doesn'r error.
Finally, delete the copied sheet (save your workbook).

1. In the book I attached, if you change the value in cell Q1, do the EMA values update?
If I change the value in Q1, change happens only in column H, there is no change in L2.

Then it begins to look like you're not using Excel 2021. You are using the desktop version aren't you? I will test that it works online too, later today.

Then it begins to look like you're not using Excel 2021. You are using the desktop version aren't you? I will test that it works online too, later today.
Yes... I am using desktop version. Let me know if I can use in Google sheet?

This is very odd. The formula and the function are both compatible with Excel 2021.
Could you try again as follows: In the workbook I attached:
Go into edit the formula in cell L2, but don't change it, then press Enter on the keyboard. What does that cell show?

If you go into Name Manager do you see this?:

Last edited:
This is very odd. The formula and the function are both compatible with Excel 2021.
Could you try again as follows: In the workbook I attached:
Go into edit the formula in cell L2, but don't change it, then press Enter on the keyboard. What does that cell show?
It shows #Name?

#### Attachments

• mema2.jpg
200.4 KB · Views: 2
When Excel prefixes functions with xlfn. (see your Name Manager pic.). it means that that version of Excel doesn't have those functions.
BYROW and LAMBDA are standard Excel worksheet functions, and, according to Excel's own compatibility checker, are available in Excel 2021.
However, I see from https://answers.microsoft.com/en-us...cel-2021/5bd84a6d-39d8-4aba-8ddf-f4f290f21a52 that a thread was created back in 2021, specifically asking about LAMBDA in Excel 2021.
It looks like you've made a one-off purchase of Office and I'd guess you bought it some time ago. I don't know what your upgrade options are for this package, but I suspect there aren't any; see what you can find.