How to Calculate Average for 12-Month Trendline

[AverageJoe2015]

Hello,

Can someone please assist in providing advice how to properly calculate an average for a twelve month trend. In the attached example, 2018 is broken up of multiple rows B3:G7 while 2019 was provided as a lump sum value.

If you take a look at Row 8, I have summed up the data and then ran an average to get 17,286.

Row 9, I took the average of each separate column B3:B7, c3:c7 and so on and took of average with 2019. Then i calculated an average of each average to get 9,572.

13,655 is taking average of each column average for 2018 and taking the average of 2019.

As you can see this data is proving me with 3 different averages. How can I create a proper average formula?

[FDibbins]

Looks like your attachment still didn't come through?
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[AverageJoe2015]

Hello,

I have uploaded the attachment.

Thank you for your help

[MrShorty]

After half a day, I will suggest that it is unclear exactly what kind of average you want to calculate. I expect that, before we can suggest a correct Excel formula, we will need you to explain exactly what these values represent and exactly what you want to do to average them. Some of that is probably less about Excel and more about statistics and the exact meaning of these numbers and exactly what you want your average to mean.

As far as the Excel programming side of the question, computing an average is usually about feeding the correct values into an AVERAGE(), or AVERAGEIFS() function. In a problem like this, the real question is what values should go into the AVERAGE()/AVERAGEIFS() function.

Sometimes it is preferable to compute the average as a sum/count function SUM(...)/COUNT(...) or SUMIFS(...)/COUNTIFS(...) or maybe even a SUMPRODUCT()/SUMPRODUCT() formula. Assuming you are already familiar with those formulas, it would again be about choosing what values go into those functions.

Those are the kinds of functions I would expect to use to calculate this average. If you will help us understand exactly how you want to calculate the average, we should be able to help you put together a suitable formula that will calculate that average.

[avk]

Try with

Please Login or Register  to view this content.

[AverageJoe2015]

Hello,

I am basically trying to take the average for the last twelve months and make a projection for the new month up to December 2019. When the new month data comes in I drop the actual data over the projected month and calculate a new forecast for the upcoming months.

To further explain the problem I am experiencing, each row makes up an individual account with row 8 being the total. In my original analysis I have two tabs that break it out by year. For 2018, you will find the data B3:G7 with a bunch of other data in between the rows. I can't take the grand total in this example because it contains other accounts that I do not want to include in my average. To simplify the inquiry I placed the rows together but in my original analysis they are in different rows and tabs. My current formula reads the following (=average(2018'I99:N102,2019'E28:J28) which gives me a relatively low average.

Ideally, I would create separate tabs for each individual account that contains data from Jan18-Dec 19. In this instance it would be easier to take the simple average equation as the account falls in the same row.

[MrShorty]

I'm still not sure I understand exactly how you want to calculate the average or what the values in the sample spreadsheet actually mean.

In my mind (to be fair, I am more of a chemist/engineer/scientist than a business finance guy), a twelve month average means total sales for the year divided by 12. If row 8 represents total sales for each month (and it is not clear to me that this is what they represent), then this average is calculated in N8. This average represents the single monthly sales value that, if each month's sales were equal to that value, I would get the same total sales for the year. Basically I am smoothing out the highs and lows and finding one single value that represents monthly sales. If that is the average you want to use, then N8 is the average that you want. I would expect that one would then use this average to assume that each month going forward gives the exact same sales value, which may or may not be accurate (is it reasonable to think that December is really that much higher than the other months?). If your end goal is forecasting, then maybe you want something more sophisticated than assuming each month gives the same sales.

Of course, I expect there are other averages that could be used, and those would be calculated differently. I don't know what the row 9 values represent (other than the average of row 3 to 7, but I don't know what is in 3 to 7, either). N9 is calculating the average of those averages with the jan-jul 2020 values from row 8. I have no idea what this average represents -- other than some kind of average of averages. I also have no idea what the average in N10 represents (other than a different average of averages).

It still seems to me that we are at the same question -- what average do you want to calculate? Exactly how do you intend to perform this analysis/forecast? Once we understand the algorithm you want to use, then we can help you program that algorithm into the spreadsheet.

[AliGW]

Administrative Note:

Sorry this is late in the day, however ...

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[AverageJoe2015]

I'm sorry this is a bit confusing. Yes, twelve month average in short is exactly what you are saying. N8 is ideally what I am trying to calculate,however, Row 3 through Row 7 are not lined up in the manner i have attached.

So what I am trying to do is take sum of the rows that make up the division (ideally r8). I.E B3+B4+B5+B6+B7 for each month and then take the average for the last 12 months. As you can see from my formula above, the calculation takes the average of all numbers including the zero's thus lowering my average.

[AliGW]

Thread title now fine - thank you.

[MrShorty]

I don't understand. If N8 is the correct calculation, what do we want to do differently in row 8? As currently set up, the 0's in rows 3:7 do not lower the average in N8, because the AVERAGE() function in N8 is not (directly) seeing those 0s. It's not clear to me what you want to do differently than you are currently doing.

[AverageJoe2015]

Row 8 is ideally what I would like to calculate more specifically N8. The problem is that the data does not fall in the same manner as the attached example. Instead 2018 data and 2019 falls in two separate tabs, 2019 already has the data lined up in H8:M8 in my example. 2018 data however does not. It has data in separate rows (similar to my example row 3- row 7) and can not create a bunch of subtotals as the data goes as far as row 300.

If you take the average(b3:g7,h8:m8) = 5,762 not 17,286.

So how I can calculate 17,286 from my current set up. Is it as easy as creating a new calculation or do I need to do something else with the data?

[MrShorty]

A lot depends on exactly how your data are currently arranged and exactly what you are trying to do.

Sometimes it seems like the best solution (especially for long term data management) is to get the data arranged in a good database format and then use pivot tables to do the summarizing.

An average is just a sum divided by a count, so maybe something like =SUM(B3:G7,H8:M8)/12 would work. The 12 can be a fixed constant because you're adding up monthly values and trying to get a monthly average over a year's worth of data, so you know there will always be 12 months to average across.

Or maybe use helper cells/rows/columns to extract the data from the different locations and bring it into a central "averaging" spreadsheet (like your example file) where you can compute the averages.

A lot depends on exactly how this needs to fit into the overall workflow.

[AverageJoe2015]

Thank you sir for your feedback. I have reconstructed my spreadsheets and started the process of calculating a 12 moving average which works as you can see in the 1st tab Q3:V13. Does anyone know of an easier way to do this?