Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-24-2010, 04:47 PM
FraserKitchell FraserKitchell is offline My weekday function aint working Windows XP My weekday function aint working Office 2007
Novice
My weekday function aint working
 
Join Date: Dec 2009
Posts: 21
FraserKitchell is on a distinguished road
Default My weekday function aint working


Hi there,

I am trying to do a fairly complex (for me) average formula of the CSE variety. My IF statement seems to be reading my WEEKDAY function value incorrectly. I'm trying to average some values only if they are between 1 and 7, and average some values that are only 1 OR 7. Please see the attached worksheet.

Column A Time/Date Stamp
Column B kWh Value
Column C Weekday Formula (Irrelevant to final formula in columns g/h)
Column F Timestamp column for Graphical Purposes
Column G Cells with wonk formula

To me this should be working. Anybody have any ideas? Thanks

Fraser
Attached Files
File Type: xls Forum.xls (37.5 KB, 16 views)
Reply With Quote
  #2  
Old 05-25-2010, 06:31 AM
CGM3 CGM3 is offline My weekday function aint working Windows XP My weekday function aint working Office 2007
Advanced Beginner
 
Join Date: Oct 2009
Posts: 38
CGM3 is on a distinguished road
Default

There is no formula in any cell of Column C in the Forum spreadsheet, just constant values (5, 6 or 7). On the other hand, they are the "weekday ordinals" for the dates given in Column A (those with April 1, 2010 have 5 -- and April 1, 2010 fell on a Thursday -- while those with April 2, 2010 have 6 -- Friday -- and those with April 3, 2010 have 7 -- Saturday). However, there are no IF() formulae.
Reply With Quote
  #3  
Old 05-25-2010, 09:59 AM
FraserKitchell FraserKitchell is offline My weekday function aint working Windows XP My weekday function aint working Office 2007
Novice
My weekday function aint working
 
Join Date: Dec 2009
Posts: 21
FraserKitchell is on a distinguished road
Default

Here is the file with the *actual* formulas. Sorry about that copy and paste to a new file didnt work out too well.

Should be fairly straightforward now.
Attached Files
File Type: xls example.xls (42.5 KB, 12 views)
Reply With Quote
  #4  
Old 05-25-2010, 11:47 AM
Kimberly Kimberly is offline My weekday function aint working Windows 7 My weekday function aint working Office 2010 (Version 14.0)
Expert
 
Join Date: May 2010
Posts: 517
Kimberly is on a distinguished road
Default

sorry, I don't get it. I see you start to average the kilowatt hours for days that aren't Sunday. I don't understand the purpose of your modulus function... it looks like you're looking at the time and seeing if the formula results = the results produced by an identical formula in Col E.
You seem determined to use a array formula and that makes me wonder.. is this for school?
Reply With Quote
  #5  
Old 05-25-2010, 12:03 PM
FraserKitchell FraserKitchell is offline My weekday function aint working Windows XP My weekday function aint working Office 2007
Novice
My weekday function aint working
 
Join Date: Dec 2009
Posts: 21
FraserKitchell is on a distinguished road
Default

Hmmm. Not for school I'm an office worker. I can say I dont understand the purpose of my modulus function either, but I am using it because I couldnt figure out any other way to get the dates to read. I'd love to do away with it. Someone tossed it out as a good methodology and I took them at their word.

I am not determined to use array formulas...or any other type. It doesnt matter to me but I need it to be bomb proof!

Thanks!

Fraser
Reply With Quote
  #6  
Old 05-25-2010, 12:07 PM
Kimberly Kimberly is offline My weekday function aint working Windows 7 My weekday function aint working Office 2010 (Version 14.0)
Expert
 
Join Date: May 2010
Posts: 517
Kimberly is on a distinguished road
Default

I would leave the Weekday functions in col C if I were you. You can always hide the column.
=AVERAGEIF(C3:C289,"<6",B3:B289)
Reply With Quote
  #7  
Old 05-25-2010, 12:35 PM
FraserKitchell FraserKitchell is offline My weekday function aint working Windows XP My weekday function aint working Office 2007
Novice
My weekday function aint working
 
Join Date: Dec 2009
Posts: 21
FraserKitchell is on a distinguished road
Default

Thats wpuldnt get me the correct answer though, as I need to average on the timestamp, and make sure it is a weekday. So I at least need an AVERAGEIFS() with two conditions. Thus I need to look at the value at noon on every weekday for a whole month (larger data set not shown) and return THAT value.
Reply With Quote
  #8  
Old 05-25-2010, 01:49 PM
ConneXionLost's Avatar
ConneXionLost ConneXionLost is offline My weekday function aint working Windows XP My weekday function aint working Office 2003
Simulacrum
 
Join Date: Jan 2010
Location: Victoria, Canada
Posts: 86
ConneXionLost is on a distinguished road
Default

I had to modify how you determine the minutes from day start since Excel will have problems comparing numbers with extreme decimals.

Anyway, give this a try:

Cheers,
Attached Files
File Type: xls WeekdayAverage.xls (56.5 KB, 14 views)
Reply With Quote
  #9  
Old 05-25-2010, 01:58 PM
Kimberly Kimberly is offline My weekday function aint working Windows 7 My weekday function aint working Office 2010 (Version 14.0)
Expert
 
Join Date: May 2010
Posts: 517
Kimberly is on a distinguished road
Default

Quote:
Originally Posted by FraserKitchell View Post
I need to look at the value at noon on every weekday for a whole month (larger data set not shown) and return THAT value.
Why are you just now saying this? How would anyone know this is your goal?
Reply With Quote
  #10  
Old 05-25-2010, 02:32 PM
FraserKitchell FraserKitchell is offline My weekday function aint working Windows XP My weekday function aint working Office 2007
Novice
My weekday function aint working
 
Join Date: Dec 2009
Posts: 21
FraserKitchell is on a distinguished road
Default

Sorry thought it would be clear from the formula that it also had to be reading the timestamp...

Didnt mean to come across as rude.
Reply With Quote
  #11  
Old 05-25-2010, 03:10 PM
Kimberly Kimberly is offline My weekday function aint working Windows 7 My weekday function aint working Office 2010 (Version 14.0)
Expert
 
Join Date: May 2010
Posts: 517
Kimberly is on a distinguished road
Default

I didn't mean to seem rude either.

In a helper column: =IF(AND(WEEKDAY(A3,2)<6,MOD(A3,0.5)=0),"yes","no")
then =AVERAGEIF(C3:C289,"yes",B3:B289)
Reply With Quote
  #12  
Old 05-25-2010, 03:35 PM
FraserKitchell FraserKitchell is offline My weekday function aint working Windows XP My weekday function aint working Office 2007
Novice
My weekday function aint working
 
Join Date: Dec 2009
Posts: 21
FraserKitchell is on a distinguished road
Default

I like that formula. And it seems to work just fine. I'll call this one solved.
Reply With Quote
  #13  
Old 05-25-2010, 04:42 PM
Kimberly Kimberly is offline My weekday function aint working Windows 7 My weekday function aint working Office 2010 (Version 14.0)
Expert
 
Join Date: May 2010
Posts: 517
Kimberly is on a distinguished road
Default

Nope, I messed up. The formula will include entries made at midnite. This one won't:
=IF(AND(WEEKDAY(A3,2)<6,MOD(A3+0.5,1)=0),"yes","no ")
Then the averageif.
Reply With Quote
Reply

Tags
average if, cse, weekday

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
My weekday function aint working lastincolumn function rdcsfd Excel Programming 7 02-08-2012 07:50 AM
My weekday function aint working FIND function, where? Yumin Word 2 04-02-2010 07:16 PM
My weekday function aint working Is this possible using the Vlookup or any other function? Delson Excel 4 02-08-2010 01:27 PM
My weekday function aint working Need help on what function to use??? Primeraman Excel 1 06-13-2006 10:16 AM
Help with Combination function sanasath Excel 0 12-13-2005 07:24 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:21 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2022, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2022 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft