You are here:

Excel/How to calculate the average number of cells not based on the data in them?

Advertisement


Question
Hi there.  I have a years worth of data in excel.  I have office 03 at my house and my work has 07 I think.  The data gets outputted as a day and time format in one cell among other things.  I have copied and pasted that data into another cell and then formatted the left cell to show the day of the week and date and the right cell to show the time only.  It goes in date order ex Tues Jan 3, Wed Jan 4, etc.  The information in each cell is a load my company completed.  There could be 10 rows of Jan 1 data, then 14 of Jan 2 data, etc.  I am trying to get an average of how many loads were completed on each day of the week.  Data in the cells (day and date or time) is irrelevant.  I just need to be able to compute the average amount of loads completed on any given monday, tuesday, wed, thurs, fri, and sat. Here is a little snip of what I am trying to explain:

Tuesday, January 03, 2012
Tuesday, January 03, 2012
Tuesday, January 03, 2012
Tuesday, January 03, 2012
Tuesday, January 03, 2012
Tuesday, January 03, 2012
Tuesday, January 03, 2012
Tuesday, January 03, 2012
Tuesday, January 03, 2012
Tuesday, January 03, 2012
Tuesday, January 03, 2012
Tuesday, January 03, 2012
Tuesday, January 03, 2012
Tuesday, January 03, 2012
Wednesday, January 04, 2012
Wednesday, January 04, 2012
Wednesday, January 04, 2012
Wednesday, January 04, 2012
Wednesday, January 04, 2012
Wednesday, January 04, 2012
Wednesday, January 04, 2012
Wednesday, January 04, 2012
Wednesday, January 04, 2012
Wednesday, January 04, 2012
Wednesday, January 04, 2012
Wednesday, January 04, 2012
Wednesday, January 04, 2012
Wednesday, January 04, 2012
Wednesday, January 04, 2012
Wednesday, January 04, 2012
Wednesday, January 04, 2012
Wednesday, January 04, 2012
Wednesday, January 04, 2012
Thursday, January 05, 2012
Thursday, January 05, 2012
Thursday, January 05, 2012
Thursday, January 05, 2012
Thursday, January 05, 2012
Thursday, January 05, 2012
Thursday, January 05, 2012
Thursday, January 05, 2012
Thursday, January 05, 2012
Thursday, January 05, 2012
Thursday, January 05, 2012
Thursday, January 05, 2012
Thursday, January 05, 2012
Thursday, January 05, 2012
Thursday, January 05, 2012
Thursday, January 05, 2012
Thursday, January 05, 2012
Thursday, January 05, 2012
Thursday, January 05, 2012
Thursday, January 05, 2012
Friday, January 06, 2012
Friday, January 06, 2012
Friday, January 06, 2012
Friday, January 06, 2012
Friday, January 06, 2012
Friday, January 06, 2012
Friday, January 06, 2012
Friday, January 06, 2012
Friday, January 06, 2012
Friday, January 06, 2012
Friday, January 06, 2012
Friday, January 06, 2012
Friday, January 06, 2012
Friday, January 06, 2012
Friday, January 06, 2012
Saturday, January 07, 2012
Saturday, January 07, 2012
Saturday, January 07, 2012
Saturday, January 07, 2012
Saturday, January 07, 2012
Saturday, January 07, 2012
Saturday, January 07, 2012
Saturday, January 07, 2012
Saturday, January 07, 2012
Saturday, January 07, 2012
Saturday, January 07, 2012
Saturday, January 07, 2012
Saturday, January 07, 2012
Saturday, January 07, 2012
Saturday, January 07, 2012
Saturday, January 07, 2012
Saturday, January 07, 2012
Saturday, January 07, 2012
Saturday, January 07, 2012
Saturday, January 07, 2012
Monday, January 09, 2012
Monday, January 09, 2012
Monday, January 09, 2012
Monday, January 09, 2012
Monday, January 09, 2012
Monday, January 09, 2012
Monday, January 09, 2012
Monday, January 09, 2012
Monday, January 09, 2012
Monday, January 09, 2012
Monday, January 09, 2012
Monday, January 09, 2012
Monday, January 09, 2012
Monday, January 09, 2012
Monday, January 09, 2012
Monday, January 09, 2012
Monday, January 09, 2012
Monday, January 09, 2012
Monday, January 09, 2012
Monday, January 09, 2012
Tuesday, January 10, 2012
Tuesday, January 10, 2012
Tuesday, January 10, 2012
Tuesday, January 10, 2012
Tuesday, January 10, 2012
Tuesday, January 10, 2012
Tuesday, January 10, 2012
Tuesday, January 10, 2012
Tuesday, January 10, 2012
Tuesday, January 10, 2012
Tuesday, January 10, 2012
Tuesday, January 10, 2012
Tuesday, January 10, 2012
Tuesday, January 10, 2012
Tuesday, January 10, 2012
Tuesday, January 10, 2012
Tuesday, January 10, 2012
Tuesday, January 10, 2012
Tuesday, January 10, 2012


So I need to be able to go through this data and say the average amount of loads completed on a tuesday was 16.5 (14 on the 3rd + 19 on the 10th / 2 = 16.5) but obviously for almost a years worth of data.  I don't want to count all the specific mondays and then tues etc.  Thanks.

Answer
I can't think of a quick/easy way to do this in EXCEL, but if you use MICROSOFT ACCESS you could pull all of this data into a query and then use a simple GROUP BY with a COUNT function to get a quick count of the number of times a specific date appears.

See this tutorial on AGGREGATE QUERIES for instructions. It's real simple if you have ACCESS.  
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

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Richard Rost

Expertise

I am the author of The Complete Idiot's Guide to Excel 2010.
 
I am happy to answer any questions about Microsoft Excel. If you have an Excel problem, let me help you with it. Also, please be sure to check the Excel Tips & Tricks and Excel Tutorials sections of my web site.
 
You can also watch my complete Excel 2010 For Beginners tutorial online which is over 90 minutes of FREE videos and an eBook.

Experience

I have been using Microsoft Excel since the very early Windows 3.1 versions. I have been teaching Microsoft Excel in the classroom since 1994, and online through computer tutorials since 2002.

Organizations
I have been volunteering on AllExperts in the categories of Microsoft Access, Visual Basic, and Weight Loss for years.

Publications
I am the author of The Complete Idiot's Guide to Excel 2010. I have created a line of computer tutorials online at www.ExcelLearningZone.com and www.599CD.com/Excel.

Education/Credentials
Personally, I am self taught. I've learned everything I know from books and trial & error. If I don't know the answer, I know how to find it.

Past/Present Clients
I have over 20,000 happy clients worldwide.

©2016 About.com. All rights reserved.