Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct and weekday?
Hello,
Array-enter (with CTRL + SHIFT + ENTER, not just ENTER!) =SUMPRODUCT(((D1:AH1)-120)*((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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct and weekday?
On Sun, 17 May 2009 08:39:06 GMT, pub wrote:
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 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct and weekday?
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)-120)*((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)-120)*((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)-120)*((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)-120)*((D1:AH1)-12)*(WEEKDAY(A1+COLUMN(D1:AH1)-4)<6)) -- Regards Roger Govier "pub" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct and weekday?
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct and weekday?
Ron Rosenfeld wrote in
: 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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct and weekday?
Bernd P wrote in news:04c8b36b-6b1c-4299-b895-
: Hello, Array-enter (with CTRL + SHIFT + ENTER, not just ENTER!) =SUMPRODUCT(((D1:AH1)-120)*((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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct and weekday?
--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. 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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct and weekday?
On Sun, 17 May 2009 18:51:43 GMT, pub wrote:
--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. 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:AH112)*(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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct and weekday?
On Sun, 17 May 2009 16:03:06 -0400, Ron Rosenfeld
wrote: On Sun, 17 May 2009 18:51:43 GMT, pub wrote: 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:AH112)*(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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct and weekday?
Ron Rosenfeld wrote in
: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct and weekday from mm/dd/yyyy format? | Excel Discussion (Misc queries) | |||
weekday() help please? | New Users to Excel | |||
WEEKDAY() function: display TEXT not numeric weekday | Excel Discussion (Misc queries) | |||
Difficult formula SUMPRODUCT,MATCH,WEEKDAY | Excel Worksheet Functions | |||
WEEKDAY using IF | Excel Discussion (Misc queries) |