Sumproduct & weeknum

[Llupo01]

Hello,

can you pls explain something to me? I am using formula

=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

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

 

[arthurbr]

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]

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]

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]

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]

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]

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.