Average Formulas working corrently

jazzista

Board Regular
Joined
Sep 15, 2014
Messages
86
Hello: I have a small data set and I want to confirm that the average formulas I am using are correct: What I want to make sure is that I am using the correct average formulas to accomplish the following:

1)monthly average
2)Monthly weighted average
3)3 month rolling average

The data set has only 2 columns. Can somebody help me on confirming that I am getting the correct results. I have attached the file per the link below. Thanks in advance. Regards


https://www.dropbox.com/s/hqfye35pvgp3ax2/averagequestion.xlsx?dl=0
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
So as far as I can tell, you're calculating the following things. You'll have to confirm whether these are what you're after or not.

1) The average of the monthly totals for the selected month and all months prior
2) The average of the individual transactions that occurred during the selected month
3) The average of the individual transactions that occurred during the selected month and the previous two months

As I've listed, one is an average of a sum of transactions, the other two are averages of individual transactions. Is this what you wanted or are you after something different?
 
Upvote 0
Hi Nimip: What I want to make sure is that the monthly weighted average and the 3 month rolling average that I am trying to calculate are using the correct formulas and therefore correct results. Thanks
 
Upvote 0
I can tell you what you've got, but you have to tell me if my descriptions match what you want, otherwise I can't tell you if they're correct or not.

You've asked for a monthly weighted average, but my understanding of the term "weighted average" doesn't really apply to this scenario... you've got fee values, but there's no other variable to multiply or "weight" them by.

Also, as I mentioned, you've got averages of two different things going on... one of monthly totals and the others of individual transaction values. Simply put:

1) is an average of the monthly totals since the beginning of the year
2), 3) are averages of what any individual fee was worth over the last 1 and 3 months, respectively

Is this what you are after? If not, give a description of what you want to calculate and I can suggest a formula to achieve that :)
 
Upvote 0
Nimip: Thanks for clarifying. I cannot obtain a weighted average since I don't have any variables to multiply: I simply misspoke. What I want to showcase is then the 3 month rolling average with the data set. Is column 3 the 3-month rolling average? Thanks again for your help and sorry for my ignorance
 
Upvote 0
Not at all and no need to apologise - a forum can be a difficult format for conversations!

So yes in column H you have a 3-month rolling average of transaction values. Your formula is ignoring any transactions from outside the last 3 months, and taking the average of what's remaining.

A simple check is to select the cells you want to take the average of, and check the average value in the status bar at the bottom of the excel window. If it doesn't have the average, you can right click the status bar and tick "average" for it to be displayed.
So, for example, if you wanted check the 3-month rolling average at June, you could select the cells with transactions from April, May and June, and if the result of your formula matches the value in the status bar, you've done it correctly.
 
Upvote 0
Hi Nimip: Thanks for checking the formulas and for the trick of the status bar. I appreciate the help. Regards and have a great day
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top