Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Functions that can be used with Sumproduct
I know that the following will work =SUMPRODUCT($F$5:$F$370,--(MOD($B$5:$B$370,7)=$S8))/SUMPRODUCT(--($F$5:$F$3700),--(MOD($B$5:$B$370,7)=$S8)) However =SUMPRODUCT($F$5:$F$370,--(WEEKNUM($B$5:$B$370)=$S8))/SUMPRODUCT(--($F$5:$F$3700),--(WEEKNUM($B$5:$B$370)=$S8)) MOD will work but WEEKNUM Will not work - is there a list of functions that will work (when dealings with vector/arrays) with sumproduct and ones that will not? Or do I have to do something special with the weeknum command to be able to work on something other than one cell? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Functions that can be used with Sumproduct
What error do you get when you try to run the formula?
-- Brevity is the soul of wit. "Brad" wrote: I know that the following will work =SUMPRODUCT($F$5:$F$370,--(MOD($B$5:$B$370,7)=$S8))/SUMPRODUCT(--($F$5:$F$3700),--(MOD($B$5:$B$370,7)=$S8)) However =SUMPRODUCT($F$5:$F$370,--(WEEKNUM($B$5:$B$370)=$S8))/SUMPRODUCT(--($F$5:$F$3700),--(WEEKNUM($B$5:$B$370)=$S8)) MOD will work but WEEKNUM Will not work - is there a list of functions that will work (when dealings with vector/arrays) with sumproduct and ones that will not? Or do I have to do something special with the weeknum command to be able to work on something other than one cell? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Functions that can be used with Sumproduct
using the - tool - formula auditing funtions give a value error when
evaluating WEEKNUM($B$5:$B$370) - I have the appropriate add-ins included in Excel "Dave F" wrote: What error do you get when you try to run the formula? -- Brevity is the soul of wit. "Brad" wrote: I know that the following will work =SUMPRODUCT($F$5:$F$370,--(MOD($B$5:$B$370,7)=$S8))/SUMPRODUCT(--($F$5:$F$3700),--(MOD($B$5:$B$370,7)=$S8)) However =SUMPRODUCT($F$5:$F$370,--(WEEKNUM($B$5:$B$370)=$S8))/SUMPRODUCT(--($F$5:$F$3700),--(WEEKNUM($B$5:$B$370)=$S8)) MOD will work but WEEKNUM Will not work - is there a list of functions that will work (when dealings with vector/arrays) with sumproduct and ones that will not? Or do I have to do something special with the weeknum command to be able to work on something other than one cell? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Functions that can be used with Sumproduct
Perhaps WEEKNUM can't be used across a range.
My understanding of its syntax is =WEEKNUM(A1,2) See this for more info: http://www.cpearson.com/excel/weeknum.htm As to which formulas can't be used with SUMPRODUCT--I don't know of any such list. Dave -- Brevity is the soul of wit. "Brad" wrote: using the - tool - formula auditing funtions give a value error when evaluating WEEKNUM($B$5:$B$370) - I have the appropriate add-ins included in Excel "Dave F" wrote: What error do you get when you try to run the formula? -- Brevity is the soul of wit. "Brad" wrote: I know that the following will work =SUMPRODUCT($F$5:$F$370,--(MOD($B$5:$B$370,7)=$S8))/SUMPRODUCT(--($F$5:$F$3700),--(MOD($B$5:$B$370,7)=$S8)) However =SUMPRODUCT($F$5:$F$370,--(WEEKNUM($B$5:$B$370)=$S8))/SUMPRODUCT(--($F$5:$F$3700),--(WEEKNUM($B$5:$B$370)=$S8)) MOD will work but WEEKNUM Will not work - is there a list of functions that will work (when dealings with vector/arrays) with sumproduct and ones that will not? Or do I have to do something special with the weeknum command to be able to work on something other than one cell? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct & Indirect Functions | Excel Worksheet Functions | |||
Combining SUMPRODUCT and RANK functions | Excel Worksheet Functions | |||
Database functions should use criteria in formula, as 1-2-3 does | Excel Worksheet Functions | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |