• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Getting 50 months exponential moving average from Data

S

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:
See formulae in cells N2:L2 of the attached.
 

Attachments

  • Chandoo56538Share_data2.xlsx
    294 KB · Views: 4
syp1677 said:
I dont see EMA function in it
Click to expand...
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.
 
p45cal said:
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.
Click to expand...
I am sorry but I am not getting when you say transfer the function.mema.jpg
 
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:

1711998093877.png

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).
 
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.
 
p45cal said:
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.
Click to expand...
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?
 
p45cal said:
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?
Click to expand...
It shows #Name?
 
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.
 
You must log in or register to reply here.
Back
Top