Sumproduct & weeknum

Llupo01

Active Member
Joined
Aug 17, 2015
Messages
296
Hello,

can you pls explain something to me? I am using formula
Code:
=SUMPRODUCT(--(WEEKNUM(A1:A5+0,21)=1))
where in A1:A5 is date 01/01/2018. Formula is working fine, in Excel 2007+ Weeknum can handle array by adding +0. I am just wondering, whats happening behind, that this same formula does not work without ,,+0". If I evaluate part
Code:
WEEKNUM(A1:A5+0,21)=1
, I get 5 x True, which by using double minus gives me desired 5x correct weeknum (which is 1 in this case) and sumproduct works...But if I evaluate that part without +0, I get VALUE error. Can you pls explain to me, what is causing adding zero in this case?

Thank you in advance for all answers. I am just trying to understand it correctly.

Regards,
Tom
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

arthurbr

Well-known Member
Joined
Dec 8, 2006
Messages
2,168
Office Version
  1. 2010
Are the dates real dates or text looking like dates ( formatting is not relevant for this)
If you remove manual alignment of the range a1:a5 the dates probably align left indicating they are text. Performing an arithmetic operation ( in this case +0) makes the text numbers
 

Llupo01

Active Member
Joined
Aug 17, 2015
Messages
296
Dates are real, but formulas behaviour is absolutely same, either date is text string, or serial number. No matter in this case. You can try. If date is text string, without +0 its Value error, with +0 its working. Same if date is serial number, without +0 its Value error, with +0 its counting.
 

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,635
Some Excel functions just do not accept range arguments, but they accept arrays. WEEKNUM can be coerced in more exotic ways, like:

=WEEKNUM(CHOOSE({1},A1:A5))
 

Llupo01

Active Member
Joined
Aug 17, 2015
Messages
296

ADVERTISEMENT

Thank you Tetra, but this is not answer to my question :) never mind, anyway thank you for your post. Well, I see, that nobody here cannot explain, how this works. I will try to contact formula author. If successfully, I will share. Thanks to you guys for at least trying, have a good day.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Thank you Tetra, but this is not answer to my question :) never mind, anyway thank you for your post. Well, I see, that nobody here cannot explain, how this works. I will try to contact formula author. If successfully, I will share. Thanks to you guys for at least trying, have a good day.
The help pages includes the following:

WEEKNUM function WEEKNUM(serial_number,[return_type]) The WEEKNUM function syntax has the following arguments:
Serial_number Required. A date within the week. Dates should be entered by using the DATE function, or as results of other formulas
or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008.

While the description does not preclude feeding the function with a range, it appears to imply that the input argument should be a single date.

1. Looks like someone (a user) seems to have experimented with:

WEEKNUM(A1:A5)

This fails of course. Upon failing, s/he must have tried applying coercion:

WEEKNUM(A1:A5+0), WEEKNUM(--(A1:A5)), etc.

and discovered the unexpected. That is, WEEKNUM processes arrays. Thus, it understands {43101;43102;43103;0;0} or A1:A5+0, but not A1:A5, a range. One can say that WEEKNUM and kindred functions need a second round of evaluation in order to process range objects.

Note that SUMPRODUCT for example evaluates range objects itself into arrays automatically if it can.

2. Or an insider from Microsoft must have "released" the information that WEEKNUM and kindred functions admits arrays.
 

Llupo01

Active Member
Joined
Aug 17, 2015
Messages
296
Awesome. Thank you. This is exactly what I was looking for, as weeknum does not behave as other non array functions while using arrays in them :) so I was just curious what is happening :) Thank you again.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,971
Messages
5,599,110
Members
414,289
Latest member
sonintebil

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top