Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average Times by Possible Named Ranges
I have a spreadsheet that I record data every day. In column A, I have (for
example) Mon, Tue, Wed, etc. Then in column H, I have recorded times (for example) 16:20, 17:30, 15:00 etc. I want averages for (All Days), (Weekdays), (Weekends) and (Tue, Wed, Thu). Right now I have a simple average formula set up and each month I have to change the formula to fit the month because the rows will change. How can I set this up so regardless of the month, the formula will only look at whether or not it's a Mon, Tue, Wed, Thu, etc in column A and average the time in column H for their respective days? I hope I explained this well enough. ANY help would be greatly appreciated Thank you, Karen |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average Times by Possible Named Ranges
Karen
Your description is not clear! 1. "In column A, I have (for example) Mon, Tue, Wed, etc." - what is etc? the whole month? Are dates associated with the day names? 2. "in column H, I have recorded times (for example) 16:20, 17:30, 15:00 etc." and "the formula will only look at whether or not it's a Mon, Tue, Wed, Thu, etc in column A and average the time in column H for their respective days?" How are the recorded times entered into column H - manually, by formula or VBA? 3. "because the rows will change" How do they change and from what to what? 4. "I have to change the formula to fit the month" What is the formula and where is it? Answers to the above may help someone to get closer to a solution for you. "Karen" wrote: I have a spreadsheet that I record data every day. In column A, I have (for example) Mon, Tue, Wed, etc. Then in column H, I have recorded times (for example) 16:20, 17:30, 15:00 etc. I want averages for (All Days), (Weekdays), (Weekends) and (Tue, Wed, Thu). Right now I have a simple average formula set up and each month I have to change the formula to fit the month because the rows will change. How can I set this up so regardless of the month, the formula will only look at whether or not it's a Mon, Tue, Wed, Thu, etc in column A and average the time in column H for their respective days? I hope I explained this well enough. ANY help would be greatly appreciated Thank you, Karen |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average Times by Possible Named Ranges
Karen;257222 Wrote: I have a spreadsheet that I record data every day. In column A, I have (for example) Mon, Tue, Wed, etc. Then in column H, I have recorded times (for example) 16:20, 17:30, 15:00 etc. I want averages for (All Days), (Weekdays), (Weekends) and (Tue, Wed, Thu). Right now I have a simple average formula set up and each month I have to change the formula to fit the month because the rows will change. How can I set this up so regardless of the month, the formula will only look at whether or not it's a Mon, Tue, Wed, Thu, etc in column A and average the time in column H for their respective days? I hope I explained this well enough. ANY help would be greatly appreciated Thank you, Karen Hi Karen, seeing your data would make things easier. You can subscribe freely and easily to our forum, where you can post a sample of your data, so we can help you faster. Hope to see you soon ! -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)* & allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=71800 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average Times by Possible Named Ranges
I'm sorry about the lack of explanation.
I have to record times in column H (manually) Below is an example of the spreadsheet for March A B H Row 1 Day Date Time Row 2 Sun 1 17:45 Row 3 Mon 2 15:30 Row 4 Tue 3 14:00 Row 5 Wed 4 16:30 When I create a spreadsheet for April, it will look like the below because the day and date change. A B H Row 1 Day Date Time Row 2 Wed 1 15:45 Row 3 Thu 2 15:00 Row 4 Fri 3 14:30 Row 5 Sat 4 15:45 I need to know 4 different averages The average of all the days (From the 1st day of the month to the last) The average of the weekdays (Mon, Tue, Wed, Thu & Fri) The average of the weekend (Fri, Sat & Sun) and the average of Tue, Wed & Thu (however manys of those days fall within that month) For example: Let's say I want to average the weekend days. Is there a way to have a formula look up column A and find which days are Fri, Sat & Sun, then look in column H for their respective times and then average those times? I hope that this was a better explanation. Thank you for your time Karen "Ron@Buy" wrote: Karen Your description is not clear! 1. "In column A, I have (for example) Mon, Tue, Wed, etc." - what is etc? the whole month? Are dates associated with the day names? 2. "in column H, I have recorded times (for example) 16:20, 17:30, 15:00 etc." and "the formula will only look at whether or not it's a Mon, Tue, Wed, Thu, etc in column A and average the time in column H for their respective days?" How are the recorded times entered into column H - manually, by formula or VBA? 3. "because the rows will change" How do they change and from what to what? 4. "I have to change the formula to fit the month" What is the formula and where is it? Answers to the above may help someone to get closer to a solution for you. "Karen" wrote: I have a spreadsheet that I record data every day. In column A, I have (for example) Mon, Tue, Wed, etc. Then in column H, I have recorded times (for example) 16:20, 17:30, 15:00 etc. I want averages for (All Days), (Weekdays), (Weekends) and (Tue, Wed, Thu). Right now I have a simple average formula set up and each month I have to change the formula to fit the month because the rows will change. How can I set this up so regardless of the month, the formula will only look at whether or not it's a Mon, Tue, Wed, Thu, etc in column A and average the time in column H for their respective days? I hope I explained this well enough. ANY help would be greatly appreciated Thank you, Karen |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Named Ranges & Calculation Times Query | Excel Discussion (Misc queries) | |||
named ranges | Excel Worksheet Functions | |||
Named Ranges | Excel Discussion (Misc queries) | |||
3D Named Ranges | Excel Worksheet Functions | |||
Like 123, allow named ranges, and print named ranges | Excel Discussion (Misc queries) |