Question description:
This user has given permission to use the problem statement for this
blog.
I need a formula that will take a row of data and calculate the monthly average increase or decrease.
The row has months and then $ for each month.
Solved by S. H. in 20 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
11/07/2018 - 08:26
Welcome to ExcelChat!
User
11/07/2018 - 08:27
hello
Excelchat Expert
11/07/2018 - 08:27
Could you explain which increase or decrease do you want to see?
Excelchat Expert
11/07/2018 - 08:28
Which numbers should I compare?
User
11/07/2018 - 08:28
I am trying to find the ave. monthly increase or decrease for the line of data
Excelchat Expert
11/07/2018 - 08:28
Okay, let's give it a try
User
11/07/2018 - 08:28
The person before me created a spreadhseet to compare the monthly increases or decreases but I think their formulas are wrong.
User
11/07/2018 - 08:29
=+((E14-D14)+(F14-E14)+(G14-F14)+(H14-G14)+(I14-H14)+(J14-I14)+(K14-J14))/7
User
11/07/2018 - 08:29
This is a sample that they used... I don't think it takes into account the entire year!
Excelchat Expert
11/07/2018 - 08:30
It actually depends on what do you want to see
Excelchat Expert
11/07/2018 - 08:30
I would propose one of two approaches
Excelchat Expert
11/07/2018 - 08:30
Either to calculate the delta of each month, and then find the average
Excelchat Expert
11/07/2018 - 08:31
Please see the row of Delta 1
User
11/07/2018 - 08:31
OK.. I see delta 1
Excelchat Expert
11/07/2018 - 08:32
Or I would use the absolute change and then find the average, in this way we would find the average changes without regarding of its direction (plus or minus)
User
11/07/2018 - 08:33
Is there a way I could put that in A2?
Excelchat Expert
11/07/2018 - 08:33
Which one do you prefer?
Excelchat Expert
11/07/2018 - 08:33
Delta 1 or Delta abs?
User
11/07/2018 - 08:33
I think that my boss would not understand the delta abs
User
11/07/2018 - 08:34
I think she sees -$7.91 or just $7.91
Excelchat Expert
11/07/2018 - 08:34
It depends what do you need from this data.. delta abs allows to show the fluctuation of your indicators
User
11/07/2018 - 08:35
The sheet she gave me has three years... with the months listed out and montly revenue added
User
11/07/2018 - 08:36
She has an average monthly increase or decrease and want's that number in the first box
Excelchat Expert
11/07/2018 - 08:36
Are you sure that you need all the calculations in one cell?
User
11/07/2018 - 08:37
But when I look at her data for 2016 and 2017 along with the formula I gave you above... I don't think its correct
Excelchat Expert
11/07/2018 - 08:38
I have Finance & Economics educations, and I can advise you that any number is true, it just depends how are you understand it
Excelchat Expert
11/07/2018 - 08:38
Regarding your boss task, she had to specify what does she mean
User
11/07/2018 - 08:38
;-)
Excelchat Expert
11/07/2018 - 08:39
But I can put here the formula which you need, if that is the case
User
11/07/2018 - 08:39
I would appreciate any help! She does not get excel and formulas and I am trying to add 2018 data in...
Excelchat Expert
11/07/2018 - 08:42
Please check A9-A11
User
11/07/2018 - 08:43
Awesome... I will plug this in... since i am not done with 2018 .. can i keep the whole formula or do i need to only put in the months that have data
Excelchat Expert
11/07/2018 - 08:43
But to my mind, if she asks for the average change of the month.. well, if month 100 and then 200, the change is 100. If month is 300, and then 200, the change is still 100
Excelchat Expert
11/07/2018 - 08:43
So I would try abs, but it is up to you :)
User
11/07/2018 - 08:43
THANK YOUUUUUU
Excelchat Expert
11/07/2018 - 08:43
You can just copy the formula down
Excelchat Expert
11/07/2018 - 08:43
and it will work for the new year a well
Excelchat Expert
11/07/2018 - 08:44
right now it works as well
Excelchat Expert
11/07/2018 - 08:44
when you enter the number, your result will appear
Excelchat Expert
11/07/2018 - 08:44
Don't forget to save the file
Excelchat Expert
11/07/2018 - 08:44
File -> Make a copy (google sheet will appear in your drive)
Excelchat Expert
11/07/2018 - 08:44
File -> Download as -> xlsx (Excel file will be downloaded)
Excelchat Expert
11/07/2018 - 08:45
Wish you good luck with your boss!
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.