I need a formula that can be used calculate a weighted average without using SUMPRODUCT (it . I've been banging my head all night, and feel that there's undoubtedly a way to utilize the ratio between the weights to calculate these values - but I am stuck.
Any help would be appreciated, and I've attached a sample.
Here's an image for those that don't want to download the image. TXgFBLR.png
d5=SUMPRODUCT((D$2:D$4)*($B$2:$B$4/SUMIF(D$2:D$4,"<>",$B$2:$B$4)))
Try this and copy towards Right
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Is there a reason why you don't want to use SUMPRODUCT?Originally Posted by justinhampton81
Audere est facere
are you ok with helper columns.
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
SUMPRODUCT is likely your best bet, its a very powerful function.
Alternatively, an average is just SUM/COUNT, so you could also weigh the sum and count only the non-blanks in a column.
Helper columns would be fine!
Pl see File.
J to N are Helper columns.
In J2, then drag upto N3
=$B2*D2
In J7, then drag upto N7
=SUM(J2:J4)/SUMIF(D2:D4,"<>",$B$2:$B$4)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks