Solved

SUMPRODUCT

Posted on 2014-04-22
6
193 Views
Last Modified: 2014-04-22
Hi Experts,

I just need to know how do I retrieve numbers from its column  instead of values of SUMPRODUCT formula.
Column A numbers Column B Numbers.
Cell D3 using this formula =SUMPRODUCT(MAX((B1:B100)*(A1:A100)))
which returns to highest multiple value from column A & B.
But I want instead of value I want Column A Value.

See attached
Book1.xlsx
0
Comment
Question by:itjockey
  • 3
  • 2
6 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 40014568
Do you mean like =(MAX(A1:A30)) ?
0
 
LVL 8

Author Comment

by:itjockey
ID: 40014663
Do you mean like =(MAX(A1:A30)) ?

Nope bcoz say if A1:A30 have max value @ Cell A19 = 500 but if I change B7 from 7 to 2500 then my =SUMPRODUCT(MAX((B1:B100)*(A1:A100))) result would be 175000. which is maximum multiple value of column A & B.  I need pair not value in formula returns i.e. 70 (Which is A7) and 2500 (which is B7)which have maximum multiple value.

As per Attached file.

Thanks
0
 
LVL 48

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 40014666
Or do you mean

=SUMPRODUCT(A1:A30*(A1:A30*B1:B30=MAX(A1:A30*B1:B30)))

Open in new window

Regards
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 48

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 500 total points
ID: 40014678
or

maybe this

=INDEX(A1:A30,MATCH(MAX(A1:A30*B1:B30),(A1:A30)*(B1:B30),0))

Open in new window


As an array Formula

Regards
0
 
LVL 8

Author Closing Comment

by:itjockey
ID: 40015048
Both are fine. it is exactly what i am looking for. if you feel to give some explanation on formulas....appreciated.

Thanks
0
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 40015299
Personnaly I would use the second formual in case ther 2 time the maximum amount you would get the sum of both

in the second formula

(A1:A30)*(B1:B30) is the array of results

Match gives me the location of the first item that equals MAX(A1:A30*B1:B30) then with index I get the Nth of the array indicated in A1:A30

Regards
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This article will show you how to use shortcut menus in the Access run-time environment.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now