Solved

Sumproduct Formula

Posted on 2014-03-21
4
251 Views
Last Modified: 2014-03-21
This formula gives me the correct answer, but is there a way to simplify it so that I do not have to have 2 SUMPRODUCTS?

=+SUMPRODUCT((A10=Data!$A$2:$A$5000)*(F$3=Data!$L$2:$L$5000)*(Data!$E$2:$E$5000)*(Data!$F$2:$F$5000))/+SUMPRODUCT((A10=Data!$A$2:$A$5000)*(F$3=Data!$L$2:$L$5000)*(Data!$F$2:$F$5000))

Thanks
0
Comment
Question by:mak345
  • 2
4 Comments
 
LVL 39

Expert Comment

by:nutsch
ID: 39945703
I believe adding 1 to the first formula will give you the same result

=SUMPRODUCT((A10=Data!$A$2:$A$5000)*(F$3=Data!$L$2:$L$5000)*(1+Data!$E$2:$E$5000)*(Data!$F$2:$F$5000))

Thomas
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 39946686
I don't believe that will give the same result, Thomas, the second SUMPRODUCT is used as the denominator in a division not an addition.

mak345,

The answer to your question is: No, there's no way to simplify in any significant way - this is the standard way to get a "weighted average" with 2 conditions. If it was me I would tidy up the syntax a little....and if you want you can use SUMIFS in place of the 2nd SUMPRODUCT (assuming you have excel 2007 or later) which will be very marginally more efficient, i.e.

=SUMPRODUCT((A10=data!$A$2:$A$5000)*(F$3=data!$L$2:$L$5000),data!$E$2:$E$5000,data!$F$2:$F$5000)/SUMIFS(data!$F$2:$F$5000,data!$A$2:$A$5000,A10,data!$L$2:$L$5000,F$3)

...but I'd say that's just "tinkering around the edges", your current formula is almost as good as it can be

regards, barry
0
 
LVL 39

Expert Comment

by:nutsch
ID: 39946688
Thanks barry, I obviously missed a bar there.
0
 

Author Closing Comment

by:mak345
ID: 39946715
Thank you!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

707 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

18 Experts available now in Live!

Get 1:1 Help Now