sum product * weighted average

marstonk

Active Member
Joined
Jan 30, 2003
Messages
273
=SUMPRODUCT(--(Development!$H$4:$H$42=A5),--(Development!$J$4:$J$42>0),Development!$P$4:$P$42)

THis is my formula but rather than adding them together I need the weighted average of the answer based on another array G:G if the above conditions are matched?

Does this make sense?

Kitty g :rolleyes:
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Do you mean...

=SUMPRODUCT(--(Development!$H$4:$H$42=A5),--(Development!$J$4:$J$42>0),Development!$P$4:$P$42)/SUM(Development!$G$2:$G$42)

or

=SUMPRODUCT(--(Development!$H$4:$H$42=A5),--(Development!$J$4:$J$42>0),Development!$P$4:$P$42)/SUMPRODUCT(--(Development!$H$4:$H$42=A5),--(Development!$J$4:$J$42>0),Development!$G$2:$G$42)

PS. I'm still a bit in dark regarding your "compounding query" in your previous post.
 
Upvote 0
Right I am on the right lines however - I need it to count the number of times the conditions are met and use this to divide by rather than it summing them.

thanks aladin.

my query yesterday has been resolved by a new approach ie what i am trying to do today. my question was about uplifting a value each year over thirty years by 2% so I guess 2^30 for the 30th year? But it didnt work anyhow. So I have changed my approach.

thanks
kitty
 
Upvote 0
hi!
Just remove the last argument!
Code:
=SUMPRODUCT(--(Development!$H$4:$H$42=A5),--(Development!$J$4:$J$42>0))
 
Upvote 0
marstonk said:
Right I am on the right lines however - I need it to count the number of times the conditions are met and use this to divide by rather than it summing them.

Not clear enough I'm afraid. The numerator is a multiconditional sum. I thought you wanted to incorporate the G range in some way. But what is in G?

my query yesterday has been resolved by a new approach ie what i am trying to do today. my question was about uplifting a value each year over thirty years by 2% so I guess 2^30 for the 30th year? But it didnt work anyhow. So I have changed my approach...

That's great. I'm curious about the solution though.
 
Upvote 0
=COUNTA((SUMPRODUCT((Development!$H$4:$H$42='SO Rents'!A5),(Development!$K$4:$K$42='SO Rents'!$F$5))),Development!$P$4:$P$42)

Hi this is what I need to do count how many times a value appears in column P when the criterea is met. Does this make sense? It is returning the same value for each row so it doesnt work any recommendations on how to get it to count the times a value occurs when the condition is met?

Thanks

struggling along kitty g
:pray:
 
Upvote 0
marstonk said:
=COUNTA((SUMPRODUCT((Development!$H$4:$H$42='SO Rents'!A5),(Development!$K$4:$K$42='SO Rents'!$F$5))),Development!$P$4:$P$42)

Hi this is what I need to do count how many times a value appears in column P when the criterea is met. Does this make sense? It is returning the same value for each row so it doesnt work any recommendations on how to get it to count the times a value occurs when the condition is met?

Thanks

struggling along kitty g
:pray:

Kitty,

=SUMPRODUCT(--(Development!$H$4:$H$42='SO Rents'!A5),--(Development!$K$4:$K$42='SO Rents'!$F$5),Development!$P$4:$P$42)

gives you a multiconditional sum, that is, every value from $P$4:$P$42 is added up when $H$4:$H$42 is A5 and $K$4:$K$42 is F5.

=SUMPRODUCT(--(Development!$H$4:$H$42='SO Rents'!A5),--(Development!$K$4:$K$42='SO Rents'!$F$5))

gives you a multiconditional count, that is, 1 when when $H$4:$H$42 is A5 and $K$4:$K$42 is F5. And all ones are summed up. Summing a bunch of individual 1's is identical to counting those 1's.

BTW, you must not omit the -- bit, which converts the truth values (TRUE/FALSE) into numbers (1/0).
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top