LinkBack URL
About LinkBacks
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?
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?
Last edited by BNCOXUK; 10-30-2013 at 01:02 PM.
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]
Yes, that is exactly right. Just multiply the largest 10 values in column A with the corresponding values in column B.Originally Posted by 6StringJazzer
A sample data is attached now in the post.
Hi,
Maybe:
=SUMPRODUCT(A2:A21*ISNUMBER(MATCH(A2:A21,INDEX(LARGE(A2:A21,ROW(INDIRECT("1:10"))),,),0))*B2:B21)
Regards
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
This awful formula agrees with my manual method.....there just has to be a better way.
Formula:
Please Login or Register to view this content.
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.
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
Yes, of course. Very concise.
Regards
Thanks for the very useful formulae. This really helped
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks