LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default sumproduct and weekday?

what am i doing wrong with my sumproduct()?

A1 = 7/1/2008 (thats July 1st 2008)
D1 = 13
E1 = 11
just to make calculating eazy i followed the 13 and 11 pattern all the
way to AH1
so
F1 = 13
G1 = 11
and so on (eventually these numbers will change randomly)

heres the formula thats failing

=SUMPRODUCT(((D1:AH1)-120)*((D1:AH1)-12)*(WEEKDAY(A1+ROW(A1:A31))<6))

so if the number is greater then 12, i subtract 12...except if i find the
number on a friday(6)

so in this case, i should get 14
but i am getting 416

i can simply so this
=SUMPRODUCT(((D1:AH1)-120)*((D1:AH1)-12))
and i get 16

but i know July 2008 had 2 fridays 7/11/8 and 7/25/8. so they should not
be counted.

evaluate formula looks ok
it has weekday(39630+{1:2:3...:30:31}
then it adds up ok
then it shows the days properly
then it shows the proper number of true Falses
then it just gets wierd

any help? please
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sumproduct and weekday from mm/dd/yyyy format? wx4usa Excel Discussion (Misc queries) 3 December 13th 08 04:15 PM
weekday() help please? Totti New Users to Excel 3 October 28th 08 04:43 PM
WEEKDAY() function: display TEXT not numeric weekday tom Excel Discussion (Misc queries) 3 November 21st 06 04:32 PM
Difficult formula SUMPRODUCT,MATCH,WEEKDAY edwardpestian Excel Worksheet Functions 7 May 8th 06 11:53 AM
WEEKDAY using IF lunker55 Excel Discussion (Misc queries) 2 December 4th 04 03:34 PM


All times are GMT +1. The time now is 03:46 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"