Weighted average from text values

[jjacker]

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.



I have also uploaded my draft, see attached.

Thanks in advance Masters, more powers and Happy Holidays!

 

[bosco_yip]

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

 

[NARAYANK991]

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

 

[jjacker]

Both works like magic! Thank you @bosco_yip & @NARAYANK991

You guys are the real Excel Masters!

 

[Peter Bartholomew]

Only the implementation differs in that I have used defined names to hold intermediate formulas.

 

[Peter Bartholomew]

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.

 

[jjacker]

@Peter Bartholomew this looks simplified solution! Thanks for your awesome input! it'll make the report more neat and easier to understand!

 

[jjacker]

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?



Thanks for your support guys!

 

[jjacker]

I'm getting an error when there are blank cells in the column range.

 

[bosco_yip]

Try...........




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

 

[Peter Bartholomew]

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 )