Excel Formula Help

[paddydive]

Hi,

I am very poor in calculation.. i need a formula to calculate total achieved and target from a data.. let me explain

I have a score sheet which as 70% Weight age on calls and 30% Weight age on process.

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o><TABLE style="WIDTH: 751pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1000><COLGROUP><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4315" width=118><COL style="WIDTH: 108pt; mso-width-source: userset; mso-width-alt: 5266" width=144><COL style="WIDTH: 87pt; mso-width-source: userset; mso-width-alt: 4242" width=116><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><COL style="WIDTH: 140pt; mso-width-source: userset; mso-width-alt: 6838" width=187><COL style="WIDTH: 122pt; mso-width-source: userset; mso-width-alt: 5924" width=162><COL style="WIDTH: 119pt; mso-width-source: userset; mso-width-alt: 5778" width=158><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 89pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20 width=118>Overall Scores</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 108pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=144>Call Related Achieved</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 87pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=116>Call Related Total</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 86pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=115>Call Overall score</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 140pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=187>Procedure Related Achieved</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 122pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=162>Procedural Related Total</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 119pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=158>Procedure Overall score</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>100.00%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>19</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>19</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>100.00%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>100.00%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>=D3*30%+G3*70%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>=B3/C3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>=E3/F3</TD></TR></TBODY></TABLE></o>
I want a formula which will give me overall total and achieved figure.

Hope i have explained my requirement clearly.

Please help.

 

[Jerry Sullivan]

Hi paddydive,

If I understand your weighting correctly the formula be the one shown in Cell K2 below. (Column A in your example was close but reversed).

Excel Workbook
	B	C	D	E	F	G	H	I	J	K
1	Call Related Achieved	Call Related Total	Call Related Score	Procedure Related Achieved	Procedure Related Total	Procedure Related Score	Overall Achieved	Overall Total	Overall Score (Simple)	Overall Score (Weighted)
2	20	20	100.00%	20	80	25.00%	40	100	40.00%	77.50%
3	20	80	25.00%	20	20	100.00%	40	100	40.00%	47.50%
Sheet1

Excel 2007

Cell Formulas
Range	Formula
G2	=E2/F2
H2	=B2+E2
I2	=C2+F2
J2	=H2/I2
K2	=70%*D2+30%*G2
D2	=B2/C2

This is harder to understand with both scores at 100%. Hopefully this example with scores at 25% and 80% helps illustrate the affect of the weighting.

Please let me know if I misunderstood.

 

[paddydive]

Hi JS411

Thank you for you help.

You have understood the 1st part of my problem.

If you check the overall achieved and overall total in coloumn J & K given in your example. It does not equal to each other.

My problem is i want the overall achive and overall total figure such a way that if you calculate it like overall achived / overall total it should be similar to colomn K.

In simple word overall achived should have weitage of 70 & 30 and same for overall total, so that division of these two will be similar to column K

hope the problem is clearly explained. let me know if you want more clarification....

 

[Jerry Sullivan]

I think I need a bit more clarification.

For the two rows of results data that I listed what should the correct value in Col K be? (You don't need to give the formula just the result you calculate separately).

If the Column K should be 40% in both cases, then you wouldn't really be using any weighting, since the answer is the same regardless of whether Call scores is 25% and Procedure scores is 100% or the opposite.

 

[paddydive]

Thanks for you time & patience.

The correct value would be 77.50%.

<TABLE style="WIDTH: 308pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=411><COLGROUP><COL style="WIDTH: 140pt; mso-width-source: userset; mso-width-alt: 6838" width=187><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" width=96><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 140pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 width=187></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=64></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 72pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl76 width=96>Correct</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 0.5pt solid" class=xl75>Overall Scores</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #95b3d7; HEIGHT: 15pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl63 height=20>Call Related Achieved</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #95b3d7; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl65>20</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 rowSpan=3>70%</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 rowSpan=6>77.50%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #95b3d7; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 height=20>Call Related Total</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #95b3d7; BORDER-TOP: black; BORDER-RIGHT: #ece9d8" class=xl65>20</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #95b3d7; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 height=20>Call Overall score</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #95b3d7; BORDER-TOP: black; BORDER-RIGHT: #ece9d8" class=xl66>100.00%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #b6dde8; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl64 height=20>Procedure Related Achieved</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #b6dde8; BORDER-TOP: black; BORDER-RIGHT: #ece9d8" class=xl70>20</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 rowSpan=3>30%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #b6dde8; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl64 height=20>Procedural Related Total</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #b6dde8; BORDER-TOP: black; BORDER-RIGHT: #ece9d8" class=xl70>80</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #b6dde8; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl64 height=20>Procedure Overall score</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #b6dde8; BORDER-TOP: black; BORDER-RIGHT: #ece9d8" class=xl71>25.00%</TD></TR></TBODY></TABLE>


Now what my requirement is that i want overall total and overall achived value which will give me the same result as 77.50% if i calculate it as overall achived / overall total.

like
overall achived = 70% weigtage call achived + 30% weighage procedure achived..
and same for overall total.

I think this is something very complicated one but any help would be approciate.

 

[Jerry Sullivan]

The formula that I showed in Cell K2 provides the correct result of 77.5%.

If I'm understanding you correctly, the method of weighting you are using to arrive at 77.5% does not use the values for Achieved and Total directly it only used the resulting ratio.

So if the Call had 1,000,000 achieved for 1,000,000 total = 100%
and the Procedure had 1 achieved for 4 total = 25%

Your desired result will still be 77.5%.
(70%*100%)+(30%*25%).

It sounds like you want to identify an Overall Achieved Numerator and Overall Total Denominator that is somehow dependent on the Total Number however whatever value you use for that will be cancelled out in the formula.

 

[paddydive]

Hi JS411

Thank you very much for your help.

You guys rock... thanks once again.