LinkBack URL
About LinkBacks
I need to be able to get the weighted average based on a date range and advisor. AVERAGE PER ADVISOR(Q1) BASED ON A DATE RANGE AND COUNT OF RESPONSES (Q1) PERO AGENT BASED ON A DATE RANGE AS WELL.
I need to be able to get the weighted average based on a date range and advisor. AVERAGE PER ADVISOR(Q1) BASED ON A DATE RANGE AND COUNT OF RESPONSES (Q1) PERO AGENT BASED ON A DATE RANGE AS WELL.
Try
B4=AVERAGEIFS(Raw!$I$2:$I$2185,Raw!$C$2:$C$2185,">="&$B$1,Raw!$C$2:$C$2185,"<="&$C$1,Raw!$J$2:$J$2185,calculate!$A4)
And drag down.
Last edited by shukla.ankur281190; 05-27-2016 at 01:10 AM.
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Thanks but I think the formula is only getting the straight average based on a date range. What we are trying to get is the weighted average.
Number of responses (Q1 count)based on advisor and date range
Scores (Q1) based on advisor and date range
Did you check properly post no #3 ....
I dont get it. Sorry whAT? can you repost the syntaxOriginally Posted by shukla.ankur281190
Check the attached file....
Looked at it and its still the straight average. I think the formula should be combination of sumproduct and sum i just dont know how to use it with criterias,
Looked at it and its still the straight average. I think the formula should be combination of sumproduct and sum i just dont know how to use it with criterias,
Looked at it and its still the straight average. I think the formula should be combination of sumproduct and sum i just dont know how to use it with criterias,
What is your expected result can you please explain???
weighted average means one piece of data multiplied by some factor then divide by the number of data points. However, you don't mention what you want to use for a weighting factor.
the weighting factor is the count of responses (q1) based on date range and advisor multiplied by the sumproduct of q1 based on advisor and date range and the count of responses(q1)
Try
B4=AVERAGEIFS(Raw!$I$2:$I$2185,Raw!$C$2:$C$2185,">="&$B$1,Raw!$C$2:$C$2185,"<="&$C$1,Raw!$J$2:$J$2185,calculate!$A4)
check the attcahment.
If it is not what you are looking for then please put expected result in excel file.
The attachment now has the overall weighted average. THis is the syntax that I need to use but I need to do this per advisor. i added a new column for volume(count) which is needed to calculate the weighted average.
=SUMPRODUCT(q1 score, q1 count)/SUM(q1 count)
Criteria = date range, advisor
Were trying to get the weighted average of each advisor
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks