Microsoft Office Forums My weekday function aint working
 Register FAQ Search Today's Posts Mark Forums Read

#1
05-24-2010, 04:47 PM
 FraserKitchell Windows XP Office 2007 Novice Join Date: Dec 2009 Posts: 21
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
 Forum.xls (37.5 KB, 16 views)
#2
05-25-2010, 06:31 AM
 CGM3 Windows XP Office 2007 Advanced Beginner Join Date: Oct 2009 Posts: 38

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.
#3
05-25-2010, 09:59 AM
 FraserKitchell Windows XP Office 2007 Novice Join Date: Dec 2009 Posts: 21

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
 example.xls (42.5 KB, 12 views)
#4
05-25-2010, 11:47 AM
 Kimberly Windows 7 Office 2010 (Version 14.0) Expert Join Date: May 2010 Posts: 517

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?
#5
05-25-2010, 12:03 PM
 FraserKitchell Windows XP Office 2007 Novice Join Date: Dec 2009 Posts: 21

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
#6
05-25-2010, 12:07 PM
 Kimberly Windows 7 Office 2010 (Version 14.0) Expert Join Date: May 2010 Posts: 517

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)
#7
05-25-2010, 12:35 PM
 FraserKitchell Windows XP Office 2007 Novice Join Date: Dec 2009 Posts: 21

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.
#8
05-25-2010, 01:49 PM
 ConneXionLost Windows XP Office 2003 Simulacrum Join Date: Jan 2010 Location: Victoria, Canada Posts: 86

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
 WeekdayAverage.xls (56.5 KB, 14 views)
#9
05-25-2010, 01:58 PM
 Kimberly Windows 7 Office 2010 (Version 14.0) Expert Join Date: May 2010 Posts: 517

Quote:
 Originally Posted by FraserKitchell 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?
#10
05-25-2010, 02:32 PM
 FraserKitchell Windows XP Office 2007 Novice Join Date: Dec 2009 Posts: 21

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.
#11
05-25-2010, 03:10 PM
 Kimberly Windows 7 Office 2010 (Version 14.0) Expert Join Date: May 2010 Posts: 517

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)
#12
05-25-2010, 03:35 PM
 FraserKitchell Windows XP Office 2007 Novice Join Date: Dec 2009 Posts: 21

I like that formula. And it seems to work just fine. I'll call this one solved.
#13
05-25-2010, 04:42 PM
 Kimberly Windows 7 Office 2010 (Version 14.0) Expert Join Date: May 2010 Posts: 517

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.

 Tags average if, cse, weekday

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post rdcsfd Excel Programming 7 02-08-2012 07:50 AM Yumin Word 2 04-02-2010 07:16 PM Delson Excel 4 02-08-2010 01:27 PM Primeraman Excel 1 06-13-2006 10:16 AM 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.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top