I have an Excel question and I hope someone can help me. I have a sheet that will hold 52 weeks of data and another sheet that is the Summary sheet, all within the same workbook. I need the Summary sheet to provide a 13 week rolling average. As I populate each week's data into the Data spreadsheet, I need the Summary sheet to automatically provide the last 13 week's average. So for example, when I populate Week 21, I need the Summary to provide me the average of Weeks 9-21. When I populate Week 22, I need to Summary sheet to automatically look at Weeks 10-22. Is this possible? If so, how can I do this? Thanks.
Try putting this in the appropriate cell in your summary sheet: =AVERAGE(OFFSET(Sheet1!B2,COUNTA(Sheet1!B2:B53)-13,0,13,1) I'm assuming your data are listed in rows B2 through B53 in sheet 1. Please adjust the formula if not. Also, if sheet 1 is named differently use your name for it.
Jet Blast has a good answer, but it all depends on how you're recording the data. Are you putting each week in its own column with a single row of data per person? Are you recording the date in a single column?
Didn't want to start a new thread, but here's a quick Excel question. What's the easiest way to delete every row in my spreadsheet that has a certain unique phrase in said row. I don't really know how to use macros, is there an other/easier way?
Need more info. Is the unique phrase the only thing in the cell? If so then filter on the column with where the phrase appears and then delete the rows. If not then what is in the rows you are trying to delete besides the unique phrase?
Yep, just thought of the auto filter. That'll work, thanks. While we're at it, I have an unrelated question regarding pivot tables in Excel. Does it matter if there are blank rows interspersed between your data?
No, blanks shouldn't matter, except the auto-selecting range will stop at a blank row. Just be sure to highlight the whole dataset when you pivot. "Blank" will be one of your fields.
Another Excel question for you guys - So I have a spreadsheet with ~18,000 rows that I'm trying to incorporate into an existing pivot table. The new spreadsheet uses the same template as my existing pivot table source spreadsheet (i.e. both have the same number of columns, same column headings, etc.). The new spreadsheet basically has a column for "Vendor Name" and another column for "Amount Paid" (there are other columns, but they aren't really relevant to my problem). Now the problem is the "Vendor Name" column isn't fully populated. Basically, it is only populated when the vendor name changes. For example, cell C2 is populated with WALMART, and the next row populated in this column is cell C20 with TARGET. In other words, C2 through C19 are all WALMART, but only C2 and C19 are populated as such. All cells in between are currently blank. Is there a formula or macro I can use to help me populate the entire column? Basically, every blank cell in this column should be populated with the vendor name directly above it. If this was only a 500 row spreadsheet, I could just manually copy and drag the vendor names, but since it's 18,000 rows, a formula would be great. Any thoughts? Thanks!
In Cell D2 put the following formula: =IF(C2="",D1,C2) Paste that sucker all the way down and it'll populate the vendor in every cell.
Another Excel question... I have a column of dates. I want to use an If Statement to categorize these dates into 5 different seasons: Summer 2007Winter 2008Summer 2008Winter 2009Summer 2009 Say the cutoff dates for Summer are (4/1 - 10/31), and therefore Winter would be (11/1 - 3/31).
Put all dates into column B where B1 is first date point. =IF(AND(B1>=04/1/2007, B1<=10/31/2007), "Summer 07", if(and(new condition), "Winter 08", if(and(new condition).......)) The parenthesis can get confusing for 5 nested if statements with 2 conditions each.
Thanks for the response, got it to work by putting in the cutoff dates to the side and referencing them in the if statement.
You can use a brute force method. Make a hidden dummy column and assign each date a number 1 through X that represents the season. Use if statements to categorize.
you're welcome, ya dates are tricky and it's best if they are referenced due to the various formats out there.
The best way to reference dates in a formula is to either hardcode them in a cell and reference that cell or use the DATE function. It takes the form of DATE(YEAR, MONTH, DAY). DATE(2010,9,1) is equal to September 1, 2010.
Needed some help here.... I have 3 columns of data (10 rows worth). I have set up a list with a header. I can sort the entire data set by specific columns. Is there anyway I could have it sort the data but not formating such as the color of the cell? So when I sort by column C, I want the the corresponding data in column A and B to sort together BUT leave all background color alone. Thanks, Qball
^ Post a screenshot of the data (mask some numbers if you don't want to share them) and what you want it to look like. It sounds like there is a function to leave formatting alone when sorting.
The only thing I could think of is that you're doing an alternate row color scheme for easy visual reference (odd rows have yellow background color, even are white). If you sort, it'll move the formats as well as the values. You'd have to use a conditional format. Highlight the range and put the following in a conditional format as a formula (assuming your range starts in Row 3): =MOD(ROW(A3),2)=1 Then choose to conditional format to be a yellow background. That would keep all the odd rows yellow regardless of any sorts you might do.
