Weighted average reverse

%3CLINGO-SUB%20id%3D%22lingo-sub-1485824%22%20slang%3D%22en-US%22%3EWeighted%20average%20reverse%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1485824%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20calculating%20weighted%20average%20based%20on%20BENEFITS%20(60%25)%20and%20EFFORTS%20(40%25)%20and%20give%20a%20total%20score.%3C%2FP%3E%3CP%3EExample%20table%3A%3C%2FP%3E%3CP%3ECRITERIA%20WEIGHTAGE%20SCORE%3C%2FP%3E%3CTABLE%20width%3D%22232px%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22110px%22%3EBENEFITS%3C%2FTD%3E%3CTD%20width%3D%2278px%22%3E%3CDIV%3E60%25%3C%2FDIV%3E%3C%2FTD%3E%3CTD%20width%3D%2243px%22%3E%3CDIV%3E10%3C%2FDIV%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22110px%22%3EEFFORTS%3C%2FTD%3E%3CTD%20width%3D%2278px%22%3E%3CDIV%3E40%25%3C%2FDIV%3E%3C%2FTD%3E%3CTD%20width%3D%2243px%22%3E%3CDIV%3E%3CSTRONG%3E%3CFONT%20color%3D%22%23FF0000%22%3E1%20%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FDIV%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22110px%22%3ETOTAL%20SCORE%3C%2FTD%3E%3CTD%20width%3D%2278px%22%3E%3CDIV%3E100%25%3C%2FDIV%3E%3C%2FTD%3E%3CTD%20width%3D%2243px%22%3E%3CDIV%3E%3CFONT%20color%3D%22%23FF0000%22%3E%3CSPAN%3E%3CSTRONG%3E6.4%3C%2FSTRONG%3E%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FDIV%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%3CFONT%20color%3D%22%23FF0000%22%3E%3CSTRONG%3EFor%20EFFORTS%20I%20want%20weighted%20calculation%20to%20be%20reversed%2C%20so%2C%20lower%20number%20should%20show%20increased%20TOTAL%20SCORE.%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3D%20-(40%25%20*%201)%20--%20do%20you%20think%20adding%20-ve%20to%20the%20calculation%20will%20give%20to%20correct%20score%20or%20is%20there%20any%20better%20formula%20in%20excel%20please%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.google.com%2Fspreadsheets%2Fd%2F13Vgydzy0QbCGkMSC4X5Z20GvI4kgVOeoJSzMaVtEKpk%2Fedit%3Fusp%3Dsharing%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3ELINK%20TO%20EXAMPLE%20FILE%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1485824%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

I am calculating weighted average based on BENEFITS (60%) and EFFORTS (40%) and give a total score.

Example table:

 

CRITERIA
WEIGHTAGE
SCORE
BENEFITS
60%​
10​
EFFORTS
40%​
1​
TOTAL SCORE
100%​
6.4

For EFFORTS I want weighted calculation to be reversed, so, lower number should show increased TOTAL SCORE.

 

= -(40% * 1) -- do you think adding -ve to the calculation will give to correct score or is there any better formula in excel please?

 

LINK TO EXAMPLE FILE

 

Thanks

1 Reply

@1Manoj 

Since it appears that you're expecting the weights to add to 100%, it would appear that the calculation is correct. But is that the case?

And are the numbers under "Score" limited to the range of 1-10? Or can they range more widely. Can they be other than integers?

 

So, in addition to defining the whole situation a bit more fully, it would help if you could spell out in an example or two what a "correct score" would look like as the "Effort" number goes up or down.