sumproduct and weekday?

[pub]

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)-12>0)*((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)-12>0)*((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

 

[Bernd P]

Hello,

Array-enter (with CTRL + SHIFT + ENTER, not just ENTER!)
=SUMPRODUCT(((D1:AH1)-12>0)*((D1:AH1)-12)*TRANSPOSE(WEEKDAY(A1+ROW
(A1:A31))<>6))
and you will get your (correct?) result 13.

The ROW() creates a vertical array which needs to get transposed...

Regards,
Bernd

 

[Ron Rosenfeld]

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)-12>0)*((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)-12>0)*((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

I think you need to explain more clearly exactly what you are trying to do.
There are inconsistencies in what you write.

First of all, July 2008 (and any month for that matter) will have at least four
Fridays (not 2).

Also, you don't write what you want to happen if the value in D1:AH1 is less
than 12. Your formula above ignores any results less than 12.

Your formula segment (a1+row(a1:a31)) will, with July 1 in A1, return July 2
through Aug 1. So you will always be ignoring the first day of the month. And
if the month has less than 31 days ...

I don't understand why you expect to have a result of 14.

Here is what your formula is doing:

D1:AH1 is 31 entries
15 of them = 11
16 of them = 13

Subtracting 12 from each entry that is greater than 12, and summing them, gives
a result of 16. (Note that entries less than 12 are evaluated to zero (0) in
your formula).

During the 31 days that you are counting, which is July 2, 2008 through August
1, 2008 inclusive, there are five Fridays (July 4, 11 , 18, 25 and August 1)
and 26 non-Fridays. 26*16 = 416 which is the result you are getting with your
formula.

????
--ron

 

[Roger Govier]

Hi

You are multiplying a vertical array by horizontal arrays, which is why you
are getting an answer of 416
You either need to Transpose the Weekday array,
=SUMPRODUCT(((D1:AH1)-12>0)*((D1:AH1)-12)*(TRANSPOSE(WEEKDAY(A1+ROW(A1:A31))<>6)))
or better still use Column instead of Row to make it horizontal as this
avoids an extra function call
=SUMPRODUCT(((D1:AH1)-12>0)*((D1:AH1)-12)*(WEEKDAY(A1+COLUMN(A1:AE1))<>6))

and you will get the answer of 16

You expect the answer to be 14, because within July, 2 of the 4 Fridays
occur when the result of subtracting 12 from the column value is 1.
However, you are not using the correct data range as you are adding 1 to
July 01 to give 02 Jul as your starting date, and hence to give 01 Aug as
your ending date. Adjust your date range by -1 as shown below
=SUMPRODUCT(((D1:AH1)-12>0)*((D1:AH1)-12)*(WEEKDAY(A1+COLUMN(A1:AE1)-1)<>6))
and your answer will be 14, as expected.

For the sake of consistency, you could use the column range of D1:AH1, but
you would need to subtract an additional 3 from the date, as below
=SUMPRODUCT(((D1:AH1)-12>0)*((D1:AH1)-12)*(WEEKDAY(A1+COLUMN(D1:AH1)-4)<>6))

 

[pub]

THANK YOU RODGER, BREND, and RON!
i woke up this morning after thinking about it, and you were right... i was
mistakenly using 7/2/8 to 8/1/8 because i was adding to the date.

Ron, the reason i said there were 2 fridays...was because i figured the
other fridays were going to get filtered out anyway.

i was unaware that using row() would do what it did...this helps me
understand other formulas i see here.

 

[pub]

I think you need to explain more clearly exactly what you are trying
to do. There are inconsistencies in what you write.

First of all, July 2008 (and any month for that matter) will have at
least four Fridays (not 2).

Also, you don't write what you want to happen if the value in D1:AH1
is less than 12. Your formula above ignores any results less than 12.

Your formula segment (a1+row(a1:a31)) will, with July 1 in A1, return
July 2 through Aug 1. So you will always be ignoring the first day of
the month. And if the month has less than 31 days ...

I don't understand why you expect to have a result of 14.

Here is what your formula is doing:

D1:AH1 is 31 entries
15 of them = 11
16 of them = 13

Subtracting 12 from each entry that is greater than 12, and summing
them, gives a result of 16. (Note that entries less than 12 are
evaluated to zero (0) in your formula).

During the 31 days that you are counting, which is July 2, 2008
through August 1, 2008 inclusive, there are five Fridays (July 4, 11 ,
18, 25 and August 1) and 26 non-Fridays. 26*16 = 416 which is the
result you are getting with your formula.

????
--ron

thanks,
if the machine runs 12 hours or less, then its running per manufacturer
specs. i filtered all those out. so i just need to see how much time
over 12 per month that we use it. i guess it breaks the machine or
something (im not much of an engineer).
and i realized my mistake on adding the date (i thought that would be the
easy part). i used every other day as an example.

 

[pub]

Hello,

Array-enter (with CTRL + SHIFT + ENTER, not just ENTER!)
=SUMPRODUCT(((D1:AH1)-12>0)*((D1:AH1)-12)*TRANSPOSE(WEEKDAY(A1+ROW
(A1:A31))<>6))
and you will get your (correct?) result 13.

The ROW() creates a vertical array which needs to get transposed...

Regards,
Bernd

thanks so much,
with the help i got here, i got the formula working.

 

[pub]

thanks,
if the machine runs 12 hours or less, then its running per
manufacturer specs. i filtered all those out. so i just need to see
how much time over 12 per month that we use it. i guess it breaks the
machine or something (im not much of an engineer).
and i realized my mistake on adding the date (i thought that would be
the easy part). i used every other day as an example.

oh and on friday, the machine is off all day, but the report spits out a 24
instead of an error. so i just filter out fridays.

 

[Ron Rosenfeld]

oh and on friday, the machine is off all day, but the report spits out a 24
instead of an error. so i just filter out fridays.

OK, this makes it more clear.

Making some assumptions

1. Some date in the month is in A1
2. D1:AH1 contain the daily runtimes for each day of that month

Given that, this **array-entered** formula should do what you want.

=SUM(TRANSPOSE(WEEKDAY(ROW(INDIRECT(A1-DAY(A1)+1&":"&
A1-DAY(A1)+32-DAY(A1-DAY(A1)+32))))<>6)*(D1:AH1>12)*(D1:AH1-12))

----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl><shift> while hitting <enter>. If you did this
correctly, Excel will place braces {...} around the formula.

This formula gives your desired answer of 14.

(You can use SUMPRODUCT, but it seems as if it also needs to be array-entered,
or else it does not ignore the Fridays).
--ron

 

[Ron Rosenfeld]

Given that, this **array-entered** formula should do what you want.

=SUM(TRANSPOSE(WEEKDAY(ROW(INDIRECT(A1-DAY(A1)+1&":"&
A1-DAY(A1)+32-DAY(A1-DAY(A1)+32))))<>6)*(D1:AH1>12)*(D1:AH1-12))

----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl><shift> while hitting <enter>. If you did this
correctly, Excel will place braces {...} around the formula.

I just noticed something and we need to change this formula. Otherwise we will
wind up with different sized arrays in the month vs the columns, which will
return an error.

For simplicity, I assumed that A1 would always contain the FIRST day of the
month in question. Given that, this **array** formula should do the trick:

=SUM(TRANSPOSE(WEEKDAY(ROW(INDIRECT(A1&":"&A1+32-DAY(A1+32))))<>6)*
(OFFSET(D1,0,0,1,DAY(A1+32-DAY(A1+32)))>12)*(OFFSET(D1,0,0,1,DAY(A1+32-DAY(A1+32)))-12))

--ron

 

[pub]

I just noticed something and we need to change this formula.
Otherwise we will wind up with different sized arrays in the month vs
the columns, which will return an error.

For simplicity, I assumed that A1 would always contain the FIRST day
of the month in question. Given that, this **array** formula should
do the trick:

=SUM(TRANSPOSE(WEEKDAY(ROW(INDIRECT(A1&":"&A1+32-DAY(A1+32))))<>6)*
(OFFSET(D1,0,0,1,DAY(A1+32-DAY(A1+32)))>12)*(OFFSET(D1,0,0,1,DAY(A1+32-
DAY(A1+32)))-12))

--ron

followup
its July and its working awesome.
thanks