Average formula for copy - pasting

GetPaidOl9

New Member
Hi Friends,

I have an Excel workbook with 2 (two) spreadsheets, 1- Daily and 2- Weekly.

On both spreadsheets, column A have dates.
On "Daily" spreadsheet, in column B, data comes daily from another workbook.
I (manually) average 7 days data and enter that (manually) in column B of "Weekly" spreadsheet.

I tried the formula, but when I 'copy', 'paste' the formula in another cells of "Weekly" spreadsheet, the cell numbers don't change by 7 days. They only change by 1.

Last edited:

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Veritan

Active Member
Hi, I'm not very clear on what you're asking, but it sounds like you're just trying to get the weekly average of some data. What I would do is have Column A contain a date that is either the first or last day of the week in question (whichever makes more sense with your data), and then have columns B:H (a total of 7 additional columns) contain the data for each day in that week. Then just use the formula =AVERAGE(Sheet1!B2:H2) and drag it down each week as you go. If this isn't what you're looking for, you'll need to provide more information about the data you're working with, including how the data is set up and providing some examples (you can sterilize the data if necessary so it doesn't show any accurate sensitive information but contains a general idea of what you're working with).

GetPaidOl9

New Member
Hello Veritan,

Sorry for late. And thanks...

I think you can get some idea for what I am trying... Here is the image - http://i.imgur.com/DfLoBLn.jpg

Thanks.

Veritan

Active Member
Okay, I came up with this formula. It's dependent on your data in Sheet2 having a date in each cell in column A next to the data you want to average that is residing in column B of Sheet2.

=IF(SUMIFS(Sheet2!\$B\$2:\$B\$15,Sheet2!\$A\$2:\$A\$15,">="&A2,Sheet2!\$A\$2:\$A\$15,"<"&A2+7)/7>0,SUMIFS(Sheet2!\$B\$2:\$B\$15,Sheet2!\$A\$2:\$A\$15,">="&A2,Sheet2!\$A\$2:\$A\$15,"<"&A2+7)/7,"")

Make sure you adjust the range references (\$A\$2:\$A\$15; \$B\$2:\$B\$15) to match your actual ranges. See if this does what you're looking for.

Edit: My formula is dividing by 7 as a constant. If you won't always be dividing by 7, you may need to substitute the constant 7 with a COUNTIFS function using the same criteria that the SUMIFS is using.

Last edited:

Veritan

Active Member
Wow, so it's early and I haven't had any coffee this morning. Completely forgot about the AVERAGEIFS function. Use this formula instead of the one above.

=IFERROR(AVERAGEIFS(Sheet2!\$B\$2:\$B\$22,Sheet2!\$A\$2:\$A\$22,">="&A4,Sheet2!\$A\$2:\$A\$22,"<"&A4+7),"")

This way you won't need to worry about any COUNTIFS, it'll figure all that out for you. Additionally, using the IFERROR statement at the beginning will return an empty string in Sheet1 if there is no data associated with that date range in Sheet2, which helps make the formula shorter and easier to understand, as well as allowing you to place the formula in all relevant cells in Sheet1 and then fill in the detail data in Sheet2 as time goes by without having to re-drag the formula down each week.

GetPaidOl9

New Member
Hello Veritan,

Thanks. Not working for me, some thing I am missing...
The result is empty cell, though there is data on 'Daily' spreadsheet.

Can you please explain / teach me -
Why you have put 'B2:B22' though I need average of 7 days?
What is '&A4' and '&A4+7' for?

If I drag this formula, only 'A4' changes and that to 'A5', nothing else.

Sorry I am new for 'these' formulas.

Veritan

Active Member
Okay, let me see if I can describe this. First, I believe this is the final version of the formula that you will use. I'm guessing on some of the ranges, but I think it should look very close to this.

=IFERROR(AVERAGEIFS(Sheet2!\$B\$2:\$B\$366,Sheet2!\$A\$2:\$A\$366,">="&A2,Sheet2!\$A\$2:\$A\$366,"<="&A2+6),"")

The point of an AVERAGEIFS (or any other IFS based function such as SUMIFS) is to allow you to select an entire range of data and perform a function (like averaging or summing), but to only do it on cells that meet certain criteria. In this case, you're trying to average 7 days worth of information. So using the formula above, the data you are trying to sum is found in the range B2:B366 on Sheet2 (or at least it will be as time progresses and you keep entering new information). This range that contains all of the information will not change. However, you only need to average a single week's worth of data, not the entire year. So the formula now requires some criteria to help it figure out which cells out of the entire range of data cells it should average. I'm assuming that every cell in the range A2:A366 on Sheet2 contains a single date, and that the dates are sequential starting from Jan. 1, 2017. So the "Sheet2!\$A\$2:\$A\$366" part of the formula tells it that the whole year's worth of dates is found in that range, and that the range will not change. An important point to note here is that the A4 I used in my original formula is a typo, it should have been A2. I'm assuming that you are putting this formula in cell B2 on Sheet1 and that cell A2 in Sheet1 contains the date Jan. 1, 2017 (this is based on the picture you linked to in an earlier post). Now, the first criteria you need to give the formula is that you only want the formula to average data cells from Sheet2 that have a date in Sheet2 that is greater than or equal to the date in A2 in Sheet1. That is what the ">="&A2 portion of the formula is doing, telling it to only average cells from Sheet2 with a date greater than or equal to the date in A2 on Sheet1. This is the starting date of the date range that you want to average. I am assuming that each consecutive cell in column A on Sheet1 will be a date that is one week later than the date just above it. So cell A3 is Jan. 8, 2017, cell A4 is Jan. 15, 2017, and so on. The second criteria is that you only want it to average a single week's worth of data. So now you need to give it an ending date for the date range that you want to average. Another note here, I adjusted the formula above so that it's a little easier to explain, so I changed the "<"&A2+7 to "<="&A2+6. In order to get the correct ending date, I am adding the 6 remaining days in a week to the starting date that is found in cell A2 on Sheet1. So for example, if I add 6 days to Jan. 1, 2017, I get Jan. 7, 2017. The date range from Jan. 1, 2017 to Jan. 7, 2017 consists of a grand total of 7 days, or 1 week's worth of information. So for the second criteria, I want the formula to only average information from Sheet2 that has a date that is less than or equal to the ending date in the correct date range. Now, it will look at the entire range of data, including any blank cells, but will only average a grand total of 7 cells from Sheet2, and will then put that average in a single cell in Sheet1. However, as you drag the formula down, it will keep looking at a different beginning date for each week's average, as the date in Column A on Sheet1 will keep advancing by 1 week. Finally, the IFERROR statement will prevent it from showing an error message if there is no data available in any of the cells on Sheet2 that meet the date requirements. You don't have to include it, but you'll get an error message displayed if you don't.

To try to put this in less computer-based language, this formula will look at all of the available information. It will then select cells for averaging that have a date greater than or equal to the first date used in the week, and less than or equal to the last date in the week. Since both of these date criteria have to be met for a cell to be selected for averaging, it will only ever select no more than 7 cells for averaging.

Take a look at this website, as well. It is a good one for explaining the SUMIFS function, which operates almost exactly like the AVERAGEIFS function. The only difference is that the SUMIFS function will sum numbers together, and the AVERAGEIFS function will average numbers together. Make sure you take a look at this site as it will be able to help you understand how the function works a lot easier than I can just using text here.

Excel SUMIF and SUMIFS Formulas Explained • My Online Training Hub

GetPaidOl9

New Member
Hello Veritan,

Thanks. Done, perfect and with the correct results.

And what the way you explained me, Hats off.

Once again Thank you very much .

Replies
4
Views
93
Replies
2
Views
131
Replies
4
Views
333
Replies
1
Views
337
Replies
11
Views
104

1,195,582
Messages
6,010,581
Members
441,557
Latest member
Jbest23

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.

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

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