Formula

[Karan001]

Hi Experts I have the following condition as shown below Example 1 is OK For me.I want solution for example- 2.

Example-1 This is ok for me as shown below
<TABLE style="WIDTH: 296pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=392><COLGROUP><COL style="WIDTH: 74pt" span=4 width=98><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: 74pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=98>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=98>C</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=98>C</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=98>D</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #006666; WIDTH: 74pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20 width=98>Pts.</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #006666; WIDTH: 74pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=98>Target</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #006666; WIDTH: 74pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=98>Actual</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #006666; WIDTH: 74pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=98>Pts.</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; WIDTH: 74pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=20 width=98>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=98>80%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=98>40%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=98>1</TD></TR></TBODY></TABLE>

In Column- D I used the below formula and got 1 Point in D-Column.
=IF(C13>B13,A13,(C13/B13)*A13)

Example -2
Requirement :
Now if the Target value is in Range then How can i Get the Points in D-Column-D.

<TABLE style="WIDTH: 296pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=392><COLGROUP><COL style="WIDTH: 74pt" span=4 width=98><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: 74pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=98>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=98>C</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=98>C</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=98>D</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #006666; WIDTH: 74pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20 width=98>Pts.</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #006666; WIDTH: 74pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=98>Target</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #006666; WIDTH: 74pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=98>Actual</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #006666; WIDTH: 74pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=98>Pts.</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; WIDTH: 74pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=20 width=98>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=98>70%-80%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=98>40%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=98>#VALUE!</TD></TR></TBODY></TABLE>

I am unable to use the formula in column D.If i am giving the Target in Range.
if my value in Colum C is with in range then it should give 2 Point other wise should calculate as per input in C column.

 

[vidplaylist]

=IF(C13>B13,A13,(C13/B13)*A13)

How you calculate if B13. 70 or 80?

What will be divider 70 or 80?

 

[vidplaylist]

http://www.mrexcel.com/forum/showthread.php?t=578207

See 2nd reply with search formula

You can use combination of left, right or mid formula with search formula

 

[vidplaylist]

L M N
2 70%-80% 40%
=IF(N33>LEFT(M33,SEARCH("-",M33,1)-1),L33,(N33/LEFT(M33,SEARCH("-",M33,1)-1))*L33)

Answer = 1.1428

 

[vidplaylist]

I only picked 70% from Cell M33 for the calculations.

 

[Karan001]

Hi Vid,
Thanks for your support.
Actually i tried your formula but it is only working for Only value not for range.

<TABLE style="WIDTH: 331pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=441><COLGROUP><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" width=85><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" span=2 width=115><COL style="WIDTH: 95pt; mso-width-source: userset; mso-width-alt: 4608" width=126><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #006666; WIDTH: 64pt; HEIGHT: 15.75pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=21 width=85>Pts.

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #006666; WIDTH: 86pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=115>Target</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #006666; WIDTH: 86pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=115>Actual</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #006666; WIDTH: 95pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl68 width=126>Pts.</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 64pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=21 width=85 align=right>2</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 86pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=115>70%-80%</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 86pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=115 align=right>80%</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 95pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl69 width=126 align=right>2.285714286</TD></TR></TBODY></TABLE>

In above table if i am using ur last formula and putting actual as 80% the points is coming more than 2 but the actual Point should be 2 only.And in contrast to that if the the value is out of the range the point should be decreases,May i know how can use theformulae so that if the actual value is within the range then system should give 2 Points otherwise the point should be decreases.

Your kind support is highly appreciated.

 

[Karan001]

Hi Experts,
Any Help Please?