Expert Solution - Excel AVERAGE Problems

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