Noloshaada dugsiga, waxaad baratay celceliska iyo habka loo xisaabiyo. Habka lagu xisaabiyo celceliska waa mid aad u fudud. Kaliya waa inaad ku darto dhammaan qiyamka xogta la bixiyay oo u qaybi natiijada tirada guud ee qiimaha xogta. Celceliska dhaqdhaqaaqa^{(Moving average)} sidoo kale waa nooc kale oo celcelis ah oo leh codsiyo nololeed oo dhab ah. Waxay leedahay codsiyo meelo badan sida suuqa saamiyada, iibka, iwm.

Celceliska guuritaanka waa saddex nooc, miisaan fudud iyo jibbaaran. Maqaalkan, waxaan ku sharixi doonaa hababka loo xisaabiyo dhammaan saddexda nooc ee celceliska dhaqdhaqaaqa ee Excel .

Akhri^(Read) : Sida loo isticmaalo MIN, Max, iyo Hawlaha celceliska ee Excel^{(How to use MIN, Max, and AVERAGE Functions of Excel)} .

Sida loo xisaabiyo celceliska dhaqdhaqaaqa^{(Moving Average)} gudaha Excel

Waxaan xisaabin doonaa celceliska dhaqdhaqaaqa Excel annagoo^(Excel) adeegsanayna laba hab. Mid ka mid ah hababka, waxaan ku isticmaali doonaa qalab ku dhex dhisan ee MS Excel iyo habka kale, waxaan isticmaali doonaa formulae.

1] Isticmaalka Xidhmada Qalabka Falanqaynta Xogta

Habkan, waxaan u isticmaali doonaa Xidhmada Qalabka Falanqaynta Xogta^{(Data Analysis Tool Pack)} ee Excel si loo xisaabiyo celceliska dhaqdhaqaaqa. MS Excel waxay la socotaa qalab lagu xisaabiyo celceliska dhaqdhaqaaqa fudud. Xirmada Qalabka Falanqaynta ^{(Analysis Tool Pack)}Xogta^(Data) waa ku-dar, taas oo macnaheedu yahay inaadan si caadi ah u haysan. Waxaan halkan ku taxnay tillaabooyinka lagu heli karo qalabkan.

1] Guji ikhtiyaarka " File ".

2] Dooro " Options ."

3] Tani waxay furi doontaa sanduuqa wada hadalka. Guji^(Click) " Kudar-Ins^(Add-Ins) ."

4] Dhinaca midig ee sanduuqa wada hadalka, waxaad arki doontaa liiska hoos u dhaca ee hoose. Ka dooro^(Select) " Excel Add-ins " ee liiska hoos-u-hoosaadka oo guji " Go " badhanka.

5] Hadda dooro " ToolPack Falanqaynta^{(Analysis ToolPack)} "oo guji " OK " badhanka.

6] Tallaabooyinka kore waxay ku rakibi doonaan Qalabka Falanqaynta Xogta^{(Data Analysis Tool)} ee MS Excel . Waxaad ka arki kartaa " Data " tab.

Akhri^(Read) : Sida loo xisaabiyo faraqa boqolkiiba ee u dhexeeya labada lambar ee Excel^{(How to calculate the percentage difference between two numbers in Excel)} .

Hadda waxaad diyaar u tahay inaad xisaabiso celceliska dhaqdhaqaaqa fudud. Si aan kuu tuso habka xisaabinta, waxaanu abuurnay xog muunad oo heerkul kala duwan ah 10 ka maalmood ee ugu horreeya bishii.

Fadlan^(Please) raac tillaabooyinka hoos ku taxan si aad u xisaabiso celceliska dhaqdhaqaaqa fudud adoo isticmaalaya Xidhmada Qalabka Falanqaynta Xogta^{(Data Analysis Tool Pack)} .

1] Guji tab " Xogta^(Data) " oo dooro ikhtiyaarka " Falanqaynta Xogta^{(Data Analysis)} " sida ku cad shaashadda kore (fiiri tallaabada 6).

2] Sanduuq yar oo wada hadal ah ayaa furmi doona, halkaas oo aad dooranayso " Dhaqdhaqaaqa Celceliska^{(Moving Average)} " doorashada oo guji OK. Waa celceliska dhaqdhaqaaqa fudud.

3] Waa inaad gelisaa kala duwanaanshaha xogta ee aad rabto inaad ku xisaabiso celceliska dhaqdhaqaaqa fudud. Xogta muunadayada, waxaanu ku haynaa xog u dhaxaysa unugyada B2 ilaa unugga B11 . Sidaa darteed, waxaan galnay xadka B2: B11 . Taas ka dib, geli " Interval ." Xaddiga wax-soo-saarka, waa inaad gelisaa ciwaanka unugga aad doonayso inaad ka hesho tirada wax-soo-saarka. Waxaan dooranay E2. Markaad dhammayso, dhagsii OK.

4] Waxaad heli doontaa natiijada. Waxaad ku arki kartaa shaashadda hoose, saddexda saf ee hore waxay muujinayaan #N/A qalad. Tani waa sababta oo ah waxaan galnay 4 gudaha " Interval ," taas oo muujinaysa inay tahay 4 maalmood SMA .

Akhri^(Read) : Sida loo xisaabiyo xiisaha fudud ee Excel^{(How to Calculate Simple Interest in Excel)} .

Habka 1 ee kor ku xusan, waxaan xisaabinay celceliska dhaqdhaqaaqa iyadoo la kaashanayo qalab Excel ah . Laakiin iyadoo la adeegsanayo habka 1, waxaan xisaabin karnaa kaliya celceliska dhaqdhaqaaqa fudud. Habka 2, waxaan ku xisaabin doonaa dhammaan saddexda nooc ee celceliska dhaqdhaqaaqa iyadoo la isticmaalayo formulae.

2] Xisaabinta ^{(Calculation)}Celceliska Dhaqdhaqaaqa Fudud^{(Simple Moving Average)} ( SMA )^(SMA) iyadoo la isticmaalayo Formula

Waxa aanu halkan ku soo qaadan doonaa xog isku mid ah.

1] Haddii aad rabto inaad xisaabiso 4 maalmood SMA , waa inaad gelisaa qaacidada soo socota unugga jiifa safka 5 ( Maalinta 4^{(Day 4)} ). Ka dib markaad geliso qaacidada, taabo " Enter ."

=AVERAGE(B2:B5)

2] Hadda, u rog cursorkaaga dhinaca midig ee hoose ee unugga la doortay. Marka calaamadeeyahaagu u beddelo astaanta " Plus ", riix oo hay dhagsiga bidix ee jiirkaaga oo u jiid unugga E11 . Tani waxay koobiyeyn doontaa caanaha unugyada kale.

3] Waxaad heli doontaa natiijadaada.

Akhri^(Read) : Sida loo xisaabiyo bedka leydi, saddex-xagal, ama goobaabin gudaha Excel^{(How to calculate the area of a Rectangle, Triangle, or Circle in Excel)} .

3] Xisaabinta ^{(Calculation)}Celceliska Dhaqdhaqaaqa Miisaanka^{(Weighted Moving Average)} leh ( WMA ) iyadoo la isticmaalayo Formula

Aynu xisaabino celceliska dhaqdhaqaaqa miisaanka leh. Mar labaad^(Again) , waxaanu qaadanaynaa isla xogta muunad isku mid ah. Si loo xisaabiyo WMA , waa in aad lahaataa miisaannada lagu qoondeeyey qiyamka gaarka ah. Qaabka lagu xisaabinayo celceliska dhaqdhaqaaqa miisaanka leh waa:

WMA = [(Latest value * weight) + (Previous value * weight) + ...] / (Sum of all weights)

Waxaan halkan ku xisaabineynaa 3 dhibcood WMA oo leh 70% culeyska ayaa loo qoondeeyay qiimihii ugu dambeeyay, 20% qiimaha wax yar ka hor, iyo 10% qiimaha ka hor kan labaad. Marka loo eego xogtan, waa inaad gelisaa qaacidooyinka soo socda unugga E4.

=(0.7*B4+0.2*B3+0.1*B2)/(0.7+0.2+0.1)

Labada unug ee hore way madhan yihiin sababtoo ah waxaanu xisaabinayna saddex dhibcood WMA . Hadda, u jiid unugga unugga ugu dambeeya sidii aan horay ugu sameynay xisaabinta SMA . Waxaad heli doontaa natiijadaada.

Akhri^(Read) : Sida loo xisaabiyo Median gudaha Excel^{(How to calculate Median in Excel)} .

4] Xisaabinta ^{(Calculation)}Celceliska Dhaqdhaqaaqa Jibbaaran^{(Exponential Moving Average)} ( EMA )^(EMA) iyadoo la isticmaalayo Formula

Si loo xisaabiyo EMA , waa inaan haysanaa qiimaha EMA ee ugu horreeya , kaas oo aan ku helno xisaabinta SMA iyo miisaanka isku-dhufashada ama simanaanta joogtada ah ( K ). Qaabka aasaasiga ah ee lagu xisaabinayo EMA waa sida soo socota:

EMA = Latest Value or Today's Value * K + Yesterday EMA Value * (1 - K)

1] Aynu soo qaadano isla jaantuskii aynu ku xisaabinay SMA . Tusaalahan, waxaan soo qaadannay K = 0.3 . Qiimaha EMA^(EMA) ee ugu horreeya wuxuu la mid yahay qiimaha SMA ee ugu horreeya . Waxaan halkaan ku xisaabineynaa 4ta maalmood ee SMA iyo EMA .

2] Hadda ku dabaq qaacidada soo socota unugga F6.

=B6*$G$1+F5*(1-$G$1)

Ogow taas, waxaan ku xirnay unugga G qaacidada sare sababtoo ah waxaan u baahanahay isla qiimaha K ee dhammaan xisaabinta EMA .

3] Hadda, Jiid^(Drag) unugga si aad u nuqul ka sameysid caanaha unugyada haray.

Taasi waa, waxaanu xisaabinay dhammaan saddexda nooc ee celceliska dhaqdhaqaaqa MS Excel . Waxaan rajeyneynaa inaad ka heshay maqaalka.

Waxa kale oo laga yaabaa inaad jeceshahay^{(You may also like)} : Sida loo xisaabiyo celceliska dhibcaha dhibcaha ama GPA ee Excel^{(How to calculate Grade Point Average or GPA in Excel)} .

Calculate Simple, Weighted, and Exponential Moving Average in Excel

In your school life, you have learned about average and the method to calculate it. The formula to calculate the average is very simple. You just have to add all the values in the given data and divide the result by the total number of values in the dаta. Moving average is also another type of average that has real-life applications. It has applications in many areas like the stock market, sales, etc.

Moving average is of three types, simple weighted and exponential. In this article, we will explain the methods to calculate all three types of moving averages in Excel.

Read: How to use MIN, Max, and AVERAGE Functions of Excel.

How to calculate Moving Average in Excel

We will calculate the moving average in Excel using two methods. In one method, we will use a built-in tool of MS Excel and in the other method, we will use formulae.

1] Using Data Analysis Tool Pack

In this method, we will use the Data Analysis Tool Pack in Excel to calculate the moving average. MS Excel comes with a tool to calculate simple moving averages. The Data Analysis Tool Pack is an add-in, which means you may not have it by default. We are listing here the steps to get this tool.

1] Click on the “File” option.

2] Select “Options.”

3] This will open a dialog box. Click on “Add-Ins.”

4] On the right panel of the dialog box, you will see a drop-down menu at the bottom. Select “Excel Add-ins” in the drop-down menu and click on the “Go” button.

5] Now select “Analysis ToolPack” and click on the “OK” button.

6] The above steps will install the Data Analysis Tool on MS Excel. You can view it in the “Data” tab.

Read: How to calculate the percentage difference between two numbers in Excel.

Now you are ready to calculate the simple moving average. To show you the calculation method, we have created sample data of varying temperatures for the first 10 days in a month.

Please follow the below-listed steps to calculate the simple moving average using Data Analysis Tool Pack.

1] Click on the “Data” tab and select the “Data Analysis” option as shown in the above screenshot (see step 6).

2] A small dialog box will open, where you have to select the “Moving Average” option and click OK. It is the simple moving average.

3] You have to enter the input range of the data for which you want to calculate the simple moving average. In our sample data, we have data ranges from cell B2 to cell B11. Therefore, we entered the range B2:B11. After that, enter the “Interval.” In the output range, you have to enter the address of the cell in which you want to get the output range. We selected E2. When you are done, click OK.

4] You will get the result. You can see in the below screenshot, the first three rows are showing #N/A error. This is because we have entered 4 in the “Interval,” which indicates that it is a 4 days SMA.

Read: How to Calculate Simple Interest in Excel.

In method 1 above, we have calculated the moving average with the help of a tool in Excel. But using method 1, we can calculate only the simple moving average. In method 2, we will calculate all three types of moving averages using formulae.

2] Calculation of Simple Moving Average (SMA) using Formula

We will take the same sample data here.

1] If you want to calculate the 4 days SMA, you have to enter the following formula in the cell that lies on row 5 (Day 4). After entering the formula, press “Enter.”

=AVERAGE(B2:B5)

2] Now, hover your cursor on the bottom right corner of the selected cell. When your cursor changes to the “Plus” icon, press and hold the left click of your mouse and drag it to the E11 cell. This will copy the formula to the other cells.

3] You will get your result.

Read: How to calculate the area of a Rectangle, Triangle, or Circle in Excel.

3] Calculation of Weighted Moving Average (WMA) using Formula

Let’s calculate the weighted moving average. Again, we are taking the same sample data. To calculate the WMA, you should have the weights assigned to the particular values. The formula to calculate the weighted moving average is:

WMA = [(Latest value * weight) + (Previous value * weight) + ...] / (Sum of all weights)

We are calculating here 3 point WMA with 70% weight is assigned to the latest value, 20% to the value just before it, and 10% to the value before the second one. According to this data, you have to enter the following formula in the E4 cell.

=(0.7*B4+0.2*B3+0.1*B2)/(0.7+0.2+0.1)

The first two cells are empty because we are calculating three-point WMA. Now, drag the cell to the last cell as we have done before in SMA calculation. You will get your result.

Read: How to calculate Median in Excel.

4] Calculation of Exponential Moving Average (EMA) using Formula

To calculate EMA, we should have the first EMA value, which we get by calculating the SMA and the weight multiplier or smoothing constant (K). The basic formula to calculate the EMA is as follows:

EMA = Latest Value or Today's Value * K + Yesterday EMA Value * (1 - K)

1] Let’s take the same table in which we have calculated the SMA. In this example, we have taken K = 0.3. The first EMA value is equal to the first SMA value. We are calculating here the 4 days SMA and EMA values.

2] Now apply the following formula to the cell F6.

=B6*$G$1+F5*(1-$G$1)

Do note that, we have locked the cell G in the above formula because we need the same value of K for all EMA calculations.

3] Now, Drag the cell to copy the formula to the remaining cells.

That’s it, we have calculated all three types of moving averages in MS Excel. We hope you enjoyed the article.

You may also like: How to calculate Grade Point Average or GPA in Excel.

Related posts

• Xisaabi leexashada caadiga ah iyo khaladka caadiga ah ee macnaha Excel

• Sida loo geliyo oo loo xisaabiyo foomamka iyo shaqooyinka Excel

• Sida loo xisaabiyo celceliska dhibcaha dhibcaha ama GPA gudaha Excel

• Sida loo isticmaalo MIN, Max, iyo AVERAGE Functions gudaha Excel

• Ku xisaabi BMI gudaha Excel adigoo isticmaalaya qaacidada xisaabinta BMI

• Sida looga soo saaro magacyada domain URL-yada Microsoft Excel

• Sidee loo xisaabiyaa korodhka ama hoos u dhaca boqolkiiba ee Excel?

• Sida loo abuuro Bracket tartanka gudaha Windows 11/10

• Sida loogu daro Trendline xaashida shaqada ee Microsoft Excel

• Sida loo geliyo shaxda firfircoon ee xaashida Excel

• Sida loo isticmaalo DCOUNT iyo DCOUNTA Function gudaha Excel

• Microsoft Excel waxay sababtaa isticmaalka CPU sare marka uu socdo Windows 10

• Sida loo beddelo qaabka faylka caadiga ah ee lagu kaydiyo Word, Excel, PowerPoint

• Sida loo abuuro Jaantuska Ururka gudaha Excel

• Sida loo isticmaalo Find and FindB functional in Excel

• Sida loo isticmaalo shaqada NETWORKDAYS gudaha Excel

• Excel, Word ama PowerPoint ma bilaabi karaan markii ugu dambeysay

• Sida loo qaybiyo hal tiir oo loo kala qaybiyo tiirar badan oo Excel ah

• Sida loo dayactiro buug-shaqeedka Excel ee xumaaday

• Sida loo xidho unugyada qaacidada Excel si loo ilaaliyo