need help with a sum formula

[Imran Azam]

Hi guys

i have kpi results data below for one person

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Alex[/TD]
[/TR]
[TR]
[TD]win rate[/TD]
[TD]56%[/TD]
[/TR]
[TR]
[TD]renewed[/TD]
[TD]5%[/TD]
[/TR]
[TR]
[TD]answered[/TD]
[TD]25%[/TD]
[/TR]
[TR]
[TD]over all Score[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I need to create a formula or come up with away to calculate the over all score.

To get the over all score you need to score each kpi first and then sum all that up
to.

The logic to score each kpi is below

If answered >= 50% then 100, winrate between 40%-49%, 75, if winrate between 20%-39%, 50 else 0

If renewed>= 20% then 100, winrate between 10%-19%, 75, if winrate between 9%-5%, 50 else 0


If winrate >= 30% then 100, winrate between 15%-29%, 75, if winrate between 5%-15%, 50 else 0


to get the over all score the above three need to be summed, the overall score for the above should show 225

is there anyway i can create 1 formula to carry this out?

thanks

 

[DanteAmor]

Try this


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:85.54px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#92d050; font-weight:bold; ">Name</td><td style="background-color:#92d050; font-weight:bold; ">Alex</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Win rate</td><td style="text-align:right; ">56%</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >renewed</td><td style="text-align:right; ">5%</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >answered</td><td style="text-align:right; ">25%</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >over all Score</td><td style="text-align:right; ">200</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formeln der Tabelle</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Zelle</td><td >Formel</td></tr><tr><td >B5</td><td >=IF(B4<20%,0,IF(B4<40%,50,IF(B4<50%,75,100))) + IF(B3<5%,0,IF(B3<10%,50,IF(B3<20%,75,100))) + IF(B2<5%,0,IF(B2<15%,50,IF(B2<30%,75,100)))</td></tr></table></td></tr></table>

 

[Eric W]

Or try:

=LOOKUP(B2,{0,0.2,0.4,0.5},{0,50,75,100})+LOOKUP(B3,{0,0.05,0.1,0.2},{0,50,75,100})+LOOKUP(B4,{0,0.05,0.15,0.3},{0,50,75,100})

Note that this formula will result in 225, while Dante's comes up with 200. I believe the difference is some confusion in your original post as to which values go with which tables. If you switch B2 and B4 in either formula, then they'll come up with the same value.

 

[DanteAmor]

Review:

If answered >= 50% then 100, winrate between 40%-49%, 75, if winrate between 20%-39%, 50 else 0
If renewed>= 20% then 100, winrate between 10%-19%, 75, if winrate between 9%-5%, 50 else 0
If winrate >= 30% then 100, winrate between 15%-29%, 75, if winrate between 5%-15%, 50 else 0

Name Alex
answered 25% ---> between 20 - 39 then 50
renewed 5% ---> between 5 - 15 then 50
win rate 56% ---> between >3 then 100

Result 200

No doubt the friend was wrong or in the references or in the result.
I took as reference the first word in blue.

We will have to wait for the OP to see what is right.

 

[Imran Azam]

thanks guys let me test this

 

[Imran Azam]

thanks guys i am sorry i made a typo in the logic this is the correct logic

If answered >= 50% then 100, answered between 40%-49%, 75, if answered between 20%-39%, 50 else 0

If renewed>= 20% then 100, renewed between 10%-19%, 75, if renewed between 9%-5%, 50 else 0


If winrate >= 30% then 100, winrate between 15%-29%, 75, if winrate between 5%-15%, 50 else 0

 

[Imran Azam]

sorry i made a mistake this is the correct logic


If answered >= 50% then 100, answered between 40%-49%, 75, if answered between 20%-39%, 50 else 0

If renewed>= 20% then 100, renewed between 10%-19%, 75, if renewed between 9%-5%, 50 else 0


If winrate >= 30% then 100, winrate between 15%-29%, 75, if winrate between 5%-15%, 50 else 0

Review:



No doubt the friend was wrong or in the references or in the result.
I took as reference the first word in blue.

We will have to wait for the OP to see what is right.

 

[Eric W]

sorry i made a mistake this is the correct logic


If answered >= 50% then 100, answered between 40%-49%, 75, if answered between 20%-39%, 50 else 0

If renewed>= 20% then 100, renewed between 10%-19%, 75, if renewed between 9%-5%, 50 else 0


If winrate >= 30% then 100, winrate between 15%-29%, 75, if winrate between 5%-15%, 50 else 0

If that's true, then Dante's original formula should work, or this:

=LOOKUP(B4,{0,0.2,0.4,0.5},{0,50,75,100})+LOOKUP(B3,{0,0.05,0.1,0.2},{0,50,75,100})+LOOKUP(B2,{0,0.05,0.15,0.3},{0,50,75,100})

You may need to set the number format in the cell to General. You should also test whichever formula you pick with several examples. In the given case, these formulas return 200.