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, 18 views)

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.

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, 14 views)

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?

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

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)

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.

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, 16 views)

Why are you just now saying this? How would anyone know this is your goal?

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.

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)

I like that formula. And it seems to work just fine. I'll call this one solved.

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.