+ Reply to Thread
Results 1 to 16 of 16

array formula question

  1. #1
    Registered User
    Join Date
    09-10-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    22

    array formula question

    Hi,

    I could use some help with the attached file -- and specifically developing the formulas where the yellow shaded cells are. I think it's a simple array formula but I'm not getting it to work.

    I am trying to total up monthly payments by general ledger code. The codes might get anywhere from 0% to 100% of the payment. On the left I have the code, then the % and then on the right are the monthly payment amounts.

    Thanks very much!
    Attached Files Attached Files

  2. #2
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: array formula question

    try this in F18 across then down.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  3. #3
    Registered User
    Join Date
    09-10-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: array formula question

    Thanks but that does not allocate the percentage portion of the GL code. The correct value for cell F18 is $1,135. Done manually the formula in F18 is: =(C8*F8)+(C9*F9)+(C10*F10)+(E12*F12)+(E13*F13)

  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: array formula question

    Try this...

    This formula entered in F18:

    =SUMPRODUCT((($B$8:$B$13=$E18)*$C$8:$C$13*F$8:F$13)+(($D$8:$D$13=$E18)*$E$8:$E$13*F$8:F$13))

    Copy down to F19 then across to I19.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: array formula question

    Try this in F18 and then copy to the right and down. This is not an array formula, although there might be an array formula that would do the same thing.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  6. #6
    Registered User
    Join Date
    09-10-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: array formula question

    Perfect! Thank you very much.

  7. #7
    Registered User
    Join Date
    09-10-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: array formula question

    Thanks! Works great. What does the -- do? Never seen that.

  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: array formula question

    You're welcome. Thanks for the feedback!

  9. #9
    Registered User
    Join Date
    09-10-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: array formula question

    How do I mark this solved?

  10. #10
    Registered User
    Join Date
    09-10-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: array formula question

    Tony -- how could I do the same but counting instead of summing?

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

    Re: array formula question

    What exactly do you want to count?

  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: array formula question

    At the top of the thread there is a menu bar. One of the selections is "Thread Tools". Open that menu and there should be a selection to mark the thread as solved.

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

    Re: array formula question

    Quote Originally Posted by Bobby789 View Post
    Thanks! Works great. What does the -- do? Never seen that.
    See this:

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

  14. #14
    Registered User
    Join Date
    09-10-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: array formula question

    I want to count all the numbers. Each number counts as 1.

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

    Re: array formula question

    In your sample all of the data is numbers.

    =COUNT(B8:I14)

    But I'm guessing that's not what you want!

  16. #16
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: array formula question

    Quote Originally Posted by Bobby789 View Post
    Thanks! Works great. What does the -- do? Never seen that.
    First of all, my formula works very similar to the one that TonyValko provided in the prior post. His formula multiplies everything together to form a single argument; I am using multiple arguments. Because I am using multiple arguments, the result of each argument has to be numeric. Using the "=" operator returns a logical value of TRUE or FALSE, not a number:

    $B$8:$B$13=$E18

    So I put the first "-" to force Excel to convert the result to a number; it will convert TRUE to 1 and FALSE to 0. But now I have a -1. So I add the second "-" to negate the effect of the first one.

    In Tony's version, the result of the "=" is multiplied times a number; that also forces conversion from logical to a number, so the "--" isn't necessary there.

+ 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. Array Formula Question
    By wpt394 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-10-2008, 11:45 AM
  2. array formula question
    By ExcelUser1 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-13-2007, 05:18 PM
  3. Complicated Array Formula Question
    By smsnead1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-28-2006, 07:58 PM
  4. [SOLVED] Formula/Array question with dates
    By Renee in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-23-2005, 09:55 PM
  5. Array Formula question
    By nicgendron in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-19-2005, 11:05 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