Weighted average reverse

[1Manoj]

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?

Thanks

 

[1Manoj]

Can anyone help please?

 

[planetpj]

Are you looking for something like this? Assuming data is layout like the above.
=SUMPRODUCT(B2:B3*C2:C3)

 

[farmerscott]

Hi,

Welcome to the Board.

What formula are you using in your "Score" col?

In your "Effort" row, I would look at using a formula like =(1- "weightage %") * factor to get your "Score" amount. This way, as "Effort %" goes down the higher the number to calculate your "Score"

FS

 

[1Manoj]

Managed to solve this by substracting 10(max scoring number a user can enter) to existing score. This will reverse the scoring affect on the calculations.