+ Reply to Thread
Results 1 to 10 of 10

Use a single formula to calculate the product of the top 10 cells with another column

  1. #1
    Forum Contributor
    Join Date
    06-18-2012
    Location
    London
    MS-Off Ver
    Excel 2019
    Posts
    339

    Use a single formula to calculate the product of the top 10 cells with another column

    I'm looking for advice on this question. I have two columns: A and B. Assume it is A1:A100, and I want to get the top 10 cells of column A and then multiple it with those values in column B. How can I make this possible in a single formula?
    Attached Files Attached Files
    Last edited by BNCOXUK; 10-30-2013 at 01:02 PM.

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    20,623

    Re: Use a single formula to calculate the product of the top 10 cells with another column

    By top 10, do you mean the 10 with the highest values? What are "those values in column B"? Do you mean the values in column B that are in the same rows as the highest values in column A?

    The best thing would be to attach a sample file and show us what result you're looking for.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Forum Contributor
    Join Date
    06-18-2012
    Location
    London
    MS-Off Ver
    Excel 2019
    Posts
    339

    Re: Use a single formula to calculate the product of the top 10 cells with another column

    Quote Originally Posted by 6StringJazzer View Post
    By top 10, do you mean the 10 with the highest values? What are "those values in column B"? Do you mean the values in column B that are in the same rows as the highest values in column A?

    The best thing would be to attach a sample file and show us what result you're looking for.
    Yes, that is exactly right. Just multiply the largest 10 values in column A with the corresponding values in column B.

    A sample data is attached now in the post.

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

    Re: Use a single formula to calculate the product of the top 10 cells with another column

    Hi,

    Maybe:

    =SUMPRODUCT(A2:A21*ISNUMBER(MATCH(A2:A21,INDEX(LARGE(A2:A21,ROW(INDIRECT("1:10"))),,),0))*B2:B21)

    Regards
    Click * below if this answer helped

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

  5. #5
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Use a single formula to calculate the product of the top 10 cells with another column

    There is something wrong. When using the formula, I get an answer of 2464.4492 but after sorting the values of column A in order then using =SUMPRODUCT((A1:A10)*(B1:B10)) I get an answer of 2913.468277 which agrees with the multiplication of the values then adding the products.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  6. #6
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Use a single formula to calculate the product of the top 10 cells with another column

    This awful formula agrees with my manual method.....there just has to be a better way.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  7. #7
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Use a single formula to calculate the product of the top 10 cells with another column

    The formula given by XOR LX will be correct if A2 is changed to A1 and B2 is changed to B1 for the example file given.

    Nice formula XOR LX!!
    Last edited by newdoverman; 10-30-2013 at 02:41 PM.

  8. #8
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Use a single formula to calculate the product of the top 10 cells with another column

    How do you want to handle duplicates? If there are ties on or around the boundary do you want to use more than 10 rows.....or should it be strictly 10? If the latter then how would you determine which rows to use (given that the column B values might be different for any duplicates)?

    XOR LX's suggestion is doing the former (might use more than 10 values in case of duplicates). You can do that more easily with this formula

    =SUMPRODUCT((A1:A20>=LARGE(A1:A20,10))+0,A1:A20,B1:B20)
    Audere est facere

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

    Re: Use a single formula to calculate the product of the top 10 cells with another column

    Yes, of course. Very concise.

    Regards

  10. #10
    Forum Contributor
    Join Date
    06-18-2012
    Location
    London
    MS-Off Ver
    Excel 2019
    Posts
    339

    Re: Use a single formula to calculate the product of the top 10 cells with another column

    Thanks for the very useful formulae. This really helped

+ 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. To find formula to calculate unused product expiry
    By decorkewl in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-29-2013, 12:07 AM
  2. [SOLVED] calculate sum of the product from one of the column
    By top1 in forum Excel General
    Replies: 12
    Last Post: 09-07-2012, 12:14 AM
  3. [SOLVED] IF Formula to calculate if product has been sent on time
    By david1987 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-06-2012, 05:22 AM
  4. Replies: 2
    Last Post: 03-15-2012, 12:11 AM
  5. Formula to calculate multiple values from product sales
    By madscooter in forum Excel General
    Replies: 0
    Last Post: 06-16-2011, 12:53 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