AboutBill Hermanson Expertise Please tell me WHICH EXCEL VERSION you are using!
DO NOT ASK ME me about Macros or VBA.
Please read my "instructions to questioners" in my full profile [use View Profile, at right], to help you write a question that I can understand, without having to ask you what you mean.
If your question contains any of the words THIS, IT, THAT, THOSE, or THEY, I likely won't understand IT. Please rewrite!
My Expertise: I am an expert at data manipulation, the use of incredibly complex logical statements, databases, combining tables and extracting data, all the LOGICAL, LOOKUP & REFERENCE functions, dynamic ranges, creating professional appearing spreadsheets, complex functions, integrated charts and visual displays, user interfaces.... I can make Excel do anything!
But PLEASE... NO MACRO or VBA QUESTIONS!
Experience 25 years development of complex spreadsheets for personal and professional use. I've developed hundreds (or thousands!) of spreadsheets in all fields, from complex engineering calculations to game scoring, financial analysis, scheduling, cost-of-doing-business, and analysis of home energy use. I even used Excel to assist in design of the flight computers presently on board the Hubble Space Telescope (1984-1991)
Education/Credentials BSEE Electrical Engineering, CU Boulder CO USA Use of spreadsheets since 1982 Boulder Valley School District, Life Long Learning, Instructor Owner & Operator of Excel Expert, LLC
Question We get 5 minute data from machines that we operate and I need to filter the data to show every 15 minutes for State Mandated reports. The problem is we might not have every 5 minutes. There could be gaps. I was manually going through, creating a pivot table, looking for all of the holes, filling those in with "dummy" data, then creating another column to number so that 00,15,30,and 45 were all 0 (using a mod formula) and filtering for 0.
Its taking me a week to do each site and I'm sure there's an easier way. I still need to have an entry for every 15 minutes, but it would be much quicker to fill the holes every 15 minutes, then every 5.
I hope that makes sense.
Thanks!
Answer Sarah,
I have more questions than answers.
I'm SURE there is an easier way than doing it manually!
So if you get data every 5 minutes in the ideal case, what do you do with each set of data? Do you take the average over the 15-minute interval? Do you add it all up within the 15-minute interval? Do you discard all but the data sets that are right on the 15-minute breaks (00, 15, 30, & 45?).
Filtering on Zero is meant to show only the values that contain zero in your added column, correct?
So you said there 'could be gaps". Do you mean that some of the data at 00, 15, 30, and 45 is MISSING? How do you "make up" the dummy data you mentioned?
When you get the data from the websites, do you have problems getting it into Excel nice and neatly? Are you spending a lot of time fussing around with the data before you can even look at it?
If you get back to me with more detailed explanations of what your data looks like and what you actually do with it, I can probably help you save a LOT of time.
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here
