+ Reply to Thread
Results 1 to 4 of 4

How to enter array constants correctly

  1. #1
    Valued Forum Contributor
    Join Date
    02-06-2013
    Location
    UK
    MS-Off Ver
    365
    Posts
    525

    How to enter array constants correctly

    Hi all, pls see the attached example.

    Why does e.g. the workday function allow dates to be array-entered like this {"1/1/2000"} while XNPV does not but does only accept the serial number equivalent of that date?

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: How to enter array constants correctly

    Good question. Who knows why MS allow such inconsistencies within their function development?

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: How to enter array constants correctly

    It's not the only case, of course. With A1 containing the date 22/11/2022:

    =A1="22/11/2022"

    and

    =SUMPRODUCT(N(A1="22/11/2022"))

    return FALSE and 0 respectively, whereas

    =COUNTIFS(A1,"22/11/2022")

    is happy to 'interpret' the text string passed as the criteria as a date.

    Regards

  4. #4
    Valued Forum Contributor
    Join Date
    02-06-2013
    Location
    UK
    MS-Off Ver
    365
    Posts
    525

    Re: How to enter array constants correctly

    Didn't think this would work but does: =XNPV(10%,{0,1000},DATEVALUE({"22/11/2022","25/11/2022"}))

    Hmm, ok.
    Thanks XOR LX.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Sumproduct with Array Constants as argument not working
    By lynnsong986 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-05-2020, 10:56 AM
  2. Adding Single Cell Array Constants
    By excelformulae in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-14-2019, 11:09 AM
  3. Concatenate Single Cell Array Constants
    By excelformulae in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-14-2019, 03:11 AM
  4. [SOLVED] Array constants
    By czlowiek121 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-18-2018, 08:43 AM
  5. Array constants in a nested VLOOKUP
    By soneaf in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-03-2016, 01:00 PM
  6. Dynamic array constants
    By michalrosa in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-02-2010, 04:01 AM
  7. Formula Array Constants, Problem
    By pegbol in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-01-2005, 02:45 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1