• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Weighted average from text values

jjacker

Member
Hi Excel Masters,

Seeking your expertise as I need to create a Project Management calculation depending if:
  1. User completed the project.
  2. Level is High, Mid, Low. Where High = 1, Mid = 0.5, and Low = 0.25
So, the only problem I have is What formula should I use for Project Completion rate, without the helper table as shown in the screenshot.

64730

I have also uploaded my draft, see attached.

Thanks in advance Masters, more powers and Happy Holidays! :)
 

Attachments

  • WeightedAverage.xlsx
    11.5 KB · Views: 27
64734

F7 :

=SUMPRODUCT((D3:D5="Done")*LOOKUP(C3:C5,{"High",1;"Low",0.25;"Mid",0.5})/SUMPRODUCT(LOOKUP(C3:C5,{"High",1;"Low",0.25;"Mid",0.5})))

Regards
 
Hi ,

Try this :

=SUMPRODUCT(($D$3:$D$5="Done")*LOOKUP($C$3:$C$5,{"High","Low","Mid"},{1,0.25,0.5})/SUM(MMULT(--($C$3:$C$5={"High","Low","Mid"}),{1;0.25;0.5})))

Narayan
 
Just had a further look at solutions from @bosco_yip and @NARAYANK991
I agree my use of a table lookup for project completion
= LOOKUP(Status, completionTable)
was overcomplicated I redefined 'projectCompletion' to be
= SIGN( Status="Done" )

In either case, my final formula was
= SUMPRODUCT(projectWeighting, projectCompletion)
though, for me,
= SUM(projectWeighting * projectCompletion)
would have done as well.

I also prefer ordering the lookup
= XLOOKUP( Level, {"High","Mid","Low"},{1,0.5,0.25} )
It seems more 'natural' not to sort.
 
Hi Masters!

I just encountered another issue while developing the report.

It seems that the formulas can't accommodate blank cells in a column range. :( how to deal with this?

64746

Thanks for your support guys!
 
Try...........

64749


In D3 enter with array (CSE) formula :

=SUM((D4:D100<>"")*(D4:D100="Done")*IFERROR(LOOKUP(C4:C100,{"High",1;"Low",0.25;"Mid",0.5}),0)/SUM(IFERROR(LOOKUP(C4:C100,{"High",1;"Low",0.25;"Mid",0.5}),0)))

p.s. Array formula is to be confirmed by pressing CTRL+SHIFT+ENTER 3 keystrokes together instead of just ENTER

Regards
Bosco
 

Attachments

  • Weighted average.xlsx
    11.1 KB · Views: 7
All that is needed is to ensure a zero value is assigned to the weighting for a blank field. For me, that is easy, since XLOOKUP has an optional [not found] parameter
= XLOOKUP(Level, weightingName, weightingValue, 0 )
Other methods of lookup may give an #N/A error, in which case nesting the formula within IFNA or IFERROR will work. If you prefer to avoid generating errors deliberately, then the outer formula could be of the form
= IF( Level<>"", lookup formula, 0 )
 
Back
Top