+ Reply to Thread
Results 1 to 16 of 16

Combine Multiple Formula With Array Into One (SUMPRODUCT Approach Fail)

  1. #1
    Registered User
    Join Date
    07-31-2012
    Location
    Buffalo, United States
    MS-Off Ver
    MS Office 2007, 2010, 2013
    Posts
    59

    Combine Multiple Formula With Array Into One (SUMPRODUCT Approach Fail)

    This is the problem I working on: http://www.excelforum.com/excel-form...the-money.html

    This is the method I will use: http://www.excelforum.com/excel-form...-in-range.html

    Below is the formula in my excel sheet:

    K11 =ROUND(I11*('Summary Sheet'!H32/H11),2)

    L11 =ROUND(MIN(K11,$C11-SUMPRODUCT($H11:J11,MMULT(ROW(11:11)^0,IF(MOD(COLUMN($H11:J11)-COLUMN($H11)+1,4)=0,1,0)))),2)

    M11 =ROUND(L11+IF(K11>L11,0,L11*(SUM(Start:End!K11-Start:End!L11)/SUMIF(Start:End!L11,Start:End!K11>Start:End!L11))),2)

    In cell I11, H11, H32, $C11 contains very simple calculation that will return Value or else "". The SUMPRODUCT in L11 is to calculate the Sum of every 4 column over and should return Value.


    What I want to do is to combine formula in K11 and L11 into M11 because it need to fit my excel form for cell reference. My only problem is when working on M11, where I want to replace the Start:End!K11 and Start:End!, which shows error that I have the formula wrong and I cannot identify the error. Anyone have any idea?

    *Sample Workbook attach in post #3*
    Last edited by jackgan; 05-16-2014 at 10:02 AM.

  2. #2
    Registered User
    Join Date
    07-31-2012
    Location
    Buffalo, United States
    MS-Off Ver
    MS Office 2007, 2010, 2013
    Posts
    59

    Re: Combine Multiple Formula With Array Into One

    Bump no response

  3. #3
    Registered User
    Join Date
    07-31-2012
    Location
    Buffalo, United States
    MS-Off Ver
    MS Office 2007, 2010, 2013
    Posts
    59

    Re: Combine Multiple Formula With Array Into One

    Bump No Respond

    I have attach a sample workbook so it might help to understand my problem.
    --------------------------------------------------------------------------------

    I have my formula in Formula Edit and try to have formula in cell M11 reference without cell K11 & L11.

    \SOV Setup/\Summary Sheet/\Formula Edit/\Start/\Sub1/\Sub2/\Sub3/\End/\Templete/
    --------------------------------------------------------------------------------

    That formula will then paste into sheet Sub1, Sub2, Sub3 in Column K, as well as Column O & S, and calculate between sheet Start:End

    \SOV Setup/\Summary Sheet/\Formula Edit/\Start/\Sub1/\Sub2/\Sub3/\End/\Templete/
    --------------------------------------------------------------------------------

    There is another sheet called Templete just in case you need to try something else but won't affect the calculation

    \SOV Setup/\Summary Sheet/\Formula Edit/\Start/\Sub1/\Sub2/\Sub3/\End/\Templete/
    --------------------------------------------------------------------------------

    I need to do so because I want to keep the table format the same with the table in Summary Sheet as there is formula that calculate from them.

    \SOV Setup/\Summary Sheet/\Formula Edit/\Start/\Sub1/\Sub2/\Sub3/\End/\Templete/
    Attached Files Attached Files

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Combine Multiple Formula With Array Into One (Sample Workbook)

    The problem with the formula in M11 is this:

    SUMIF(Start:End!L11,Start:End!K11>Start:End!L11)
    SUMIF won't work across sheets like that. Also, it looks like the arguments are backwards.

    My recommendation would be to use intermediate formulas on each sheet in the same cells then just sum those cells. For example, on each of the Sub sheets enter this formula in cell X1:

    =IF(K11>L11,L11,0)

    Then you would replace the SUMIF with this:

    SUM(Start:End!X1)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Registered User
    Join Date
    07-31-2012
    Location
    Buffalo, United States
    MS-Off Ver
    MS Office 2007, 2010, 2013
    Posts
    59

    Re: Combine Multiple Formula With Array Into One (Sample Workbook)

    I try not to have column K and L in the sheet as I want to keep the table format same as Summary Sheet.

    Here is another workbook with the concept behind it and see if you can help me simplify the calculation or argument to make it work? Really appreciate your reply on my problem :D
    Attached Files Attached Files

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Combine Multiple Formula With Array Into One (Sample Workbook)

    My suggestion in reply #4 is your best option.

    Otherwise, it would be extremely complicated and the resulting formula would be very long and complex.

  7. #7
    Registered User
    Join Date
    07-31-2012
    Location
    Buffalo, United States
    MS-Off Ver
    MS Office 2007, 2010, 2013
    Posts
    59

    Re: Combine Multiple Formula With Array Into One (Sample Workbook)

    Okay I think I will go with your option. But since you mention about complicated and long formula, is that with SUMPRODUCT? I found online with this article and maybe it helps? http://office.microsoft.com/en-us/ex...005200020.aspx

    That will be great if you would like to try with that new info because my first trial end up with 1000++ character in the formula lols. If it is still not possible please do let me know so I know that approach will reach a dead end.

    Anyway thanks for letting me know the SUMIF don't work that way or else I would never figure it out

  8. #8
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Combine Multiple Formula With Array Into One (Sample Workbook)

    Quote Originally Posted by jackgan View Post
    Okay I think I will go with your option. But since you mention about complicated and long formula, is that with SUMPRODUCT?
    Yes.

    Here's an example of using SUMPRODUCT across multiple sheets:

    http://tinyurl.com/d8apqk

    It's not pretty!

  9. #9
    Registered User
    Join Date
    07-31-2012
    Location
    Buffalo, United States
    MS-Off Ver
    MS Office 2007, 2010, 2013
    Posts
    59

    Re: Combine Multiple Formula With Array Into One (Sample Workbook)

    Oh it is ugly...lols

    I have attach the formula I modify for SUMPRODUCT but it show me #REF! error when I try to calculate using sheet array, besides the Start:End!L11 as I don't know how to convert from the normal L11. What can you tell me about the error?
    Attached Files Attached Files

  10. #10
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Combine Multiple Formula With Array Into One (Sample Workbook)

    OK, I downloaded your file but I'm not sure what I'm looking at!

    What are we trying to do with this?

  11. #11
    Registered User
    Join Date
    07-31-2012
    Location
    Buffalo, United States
    MS-Off Ver
    MS Office 2007, 2010, 2013
    Posts
    59

    Re: Combine Multiple Formula With Array Into One (Sample Workbook)

    Column B indicate the cell number or array that represent in the formula and Column D is the formula which cell D6 (target cell) represent the formula M11 in the sample workbook.

    What I am trying to do is to have formula in cell D6 to reference without any K11 and L11 value. Or in another words, put formula from K11, L11, Start:End!K11, Start:End!L11 into cell D6 formula.

  12. #12
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Combine Multiple Formula With Array Into One (Sample Workbook)

    So, you want to get this portion of the longer formula working correctly:

    SUMPRODUCT(Start:End!L11,IF(Start:End!K11>Start:End!L11,1,0))

    You want a count of how many times cell K11 is greater than cell L11 on the sheets between Start and End?

    If that's the case then you'll have to rearrange the sheets as SUMPRODUCT won't accept across sheet references like Start:End.

    You would have to specifically list all the sheet names individually.

  13. #13
    Registered User
    Join Date
    07-31-2012
    Location
    Buffalo, United States
    MS-Off Ver
    MS Office 2007, 2010, 2013
    Posts
    59

    Re: Combine Multiple Formula With Array Into One (Sample Workbook)

    My understanding for SUMPRODUCT is that it calculate array to array so the first array will be array across the sheet from L11 and the second array will be 1 or 0 base on TRUE or FALSE of the statement. What I try to do is like this:

    K11 =ROUND(I11*('Summary Sheet'!H32/H11),2)

    L11 =ROUND(MIN(K11,$C11-SUMPRODUCT($H11:J11,MMULT(ROW(11:11)^0,IF(MOD(COLUMN($H11:J11)-COLUMN($H11)+1,4)=0,1,0)))),2)

    Hence,
    L11 =ROUND(MIN(ROUND(I11*('Summary Sheet'!H32/H11),2),$C11-SUMPRODUCT($H11:J11,MMULT(ROW(11:11)^0,IF(MOD(COLUMN($H11:J11)-COLUMN($H11)+1,4)=0,1,0)))),2)

    And,
    M11 =ROUND(L11+IF(K11>L11,0,L11*(SUM(Start:End!K11-Start:End!L11)/SUMPRODUCT(Start:End!L11,IF(Start:End!K11>Start:End!L11,1,0)))),2)

    Hence,
    M11 =ROUND(ROUND(MIN(ROUND(I11*('Summary Sheet'!H32/H11),2),$C11-SUMPRODUCT($H11:J11,MMULT(ROW(11:11)^0,IF(MOD(COLUMN($H11:J11)-COLUMN($H11)+1,4)=0,1,0)))),2)+IF(ROUND(I11*('Summary Sheet'!H32/H11),2)>ROUND(MIN(ROUND(I11*('Summary Sheet'!H32/H11),2),$C11-SUMPRODUCT($H11:J11,MMULT(ROW(11:11)^0,IF(MOD(COLUMN($H11:J11)-COLUMN($H11)+1,4)=0,1,0)))),2),0,ROUND(MIN(ROUND(I11*('Summary Sheet'!H32/H11),2),$C11-SUMPRODUCT($H11:J11,MMULT(ROW(11:11)^0,IF(MOD(COLUMN($H11:J11)-COLUMN($H11)+1,4)=0,1,0)))),2)*(SUM(Start:End!K11-Start:End!L11)/SUMPRODUCT(Start:End!L11,IF(Start:End!K11>Start:End!L11,1,0)))),2)

    It will be hard to understand by looking at it so just don't read it and all I did is just copy and paste that relative cell formula. The green part is what I try to figure out but seems like excel don't run calculation like that isn't it? I think I'll just give up trying that approach lols. And actually I have two questions on SUMPRODUCT from the following formula:

    =SUMPRODUCT(--(IF(...............))

    What is the purpose of -- in a SUMPRODUCT? I saw it online that someone write the formula with that.


    =SUMPRODUCT('Start[Start]:End[End]'!A1,................)

    Why (and it happens) when I enter a formula as array it then shows [Start] and [End] in the formula?

    Again thank you Tony for bearing with me for so long of time

  14. #14
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Combine Multiple Formula With Array Into One (Sample Workbook)

    That formula for M11 is

    I still think you should give up on the SUMPRODUCT across the sheets and use intermediate formulas on each of the sheets and simply sum these cells. Trust me, it will be easier by orders of magnitude!

    For more info on the SUMPRODUCT function see this:

    http://xldynamic.com/source/xld.SUMPRODUCT.html

  15. #15
    Registered User
    Join Date
    07-31-2012
    Location
    Buffalo, United States
    MS-Off Ver
    MS Office 2007, 2010, 2013
    Posts
    59

    Re: Combine Multiple Formula With Array Into One (Sample Workbook)

    Oh I love your reaction lols. I had gave up on the SUMPRODUCT approach and will just ask our guy to do their own calculation. Anyway thanks for the information and it is very helpful.

  16. #16
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Combine Multiple Formula With Array Into One (Sample Workbook)

    You're welcome. Thanks for the feedback!

+ 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. Combine Multiple scripts into an array
    By Thunderer in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-04-2014, 11:53 AM
  2. Combine Array formula with VBA code
    By kabammi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-14-2012, 04:55 PM
  3. Replies: 3
    Last Post: 08-07-2011, 06:52 AM
  4. Vlookup in combine with an Array Formula
    By Mike2 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 11-28-2008, 09:03 AM
  5. Replies: 1
    Last Post: 08-25-2005, 03:43 AM

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