Defining an Array Within a Formula
Results 1 to 4 of 4

Thread: Defining an Array Within a Formula

  1. #1
    New Member
    Join Date
    Mar 2012
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Defining an Array Within a Formula

    Greetings all,

    I'm looking to multiply the values returned by these 5 rounded sumproducts by the array created by "Volume!Y$24:Y$28/Volume!Y$63", but I don't know how to get the formula to recognize those 5 returned values as an array. Thank you for our help in advance!

    =IFERROR(SUMPRODUCT(ROUNDDOWN(SUMPRODUCT((HPU!$B$6:$B$58=$B6)*(HPU!$D$5:$J$5=YEAR(Y$3))*HPU!$D$6:$J$58),1),ROUNDDOWN(SUMPRODUCT((HPU!$B$6:$B $58=$B6)*(HPU!$M$5:$S$5=YEAR(Y$3))*HPU!$M$6:$S$58),1),ROUNDDOWN(SUMPRODUCT((HPU!$B$6:$B$58=$B6)*(HPU!$V$5:$AB$5=YEAR(Y$3 ))*HPU!$V$6:$AB$58),1),ROUNDDOWN(SUMPRODUCT((HPU!$B$6:$B$58=$B6)*(HPU!$AE$5:$AK$5=YEAR(Y$3))*HPU!$AE$6:$AK$58),1),ROUNDD OWN(SUMPRODUCT((HPU!$B$6:$B$58=$B6)*(HPU!$AN$5:$AT$5=YEAR(Y$3))*HPU!$AN$6:$AT$58),1),Volume!Y$24:Y$28/Volume!Y$63),0)*Volume!Y$64

  2. #2
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,239
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Defining an Array Within a Formula

    To create a vertical array with 5 single results you can try something like

    CHOOSE({1;2;3;4;5},result1,result2,result3,result4,result5)

    M.

  3. #3
    New Member
    Join Date
    Mar 2012
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Defining an Array Within a Formula

    Thank you, Marcelo. This worked!

  4. #4
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,239
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Defining an Array Within a Formula

    You are welcome. Glad to help.

    M.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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