Excel Formula ... Very hard to find the solution

[vijaychennai]

Hello all,

I want to get points based on below table.

Excel Workbook
	B	C	D	E	F	G	H	I	J	K	L
1	A			B			C			D
2	100%	160		100%	80		100%	53.33		100%	44.44
3	120%	192		120%	96		120%	64		120%	53.33
4
5	Less than 25 %	0		Lessthan 50 %	0		Less than 75%	0		70% to 89.99%	0
6										0 to 69.99%	-5
Sheet1

I want to get points for the below.

Excel Workbook
	A	B	C
1	Client	Percentage	Points
2	A	40%
3	A	24%
4	A	125%
5	B	80%
6	B	49%
7	B	121%
8	C	76%
9	C	74%
10	C	140%
11	D	75%
12	D	62%
13	D	140%
Sheet2

Condition:

Group A:

Lessthan 25% points "0" and greater than 120% Points "192"

Group B :

Lessthan 50% points "0" and greater than 120% Points "96"


Group C :

Lessthan 75% points "0" and greater than 120% Points "64"


Group D :

Lessthan 70% points "-5"
greater than 120% Points "53.33"
70 to 89.99 % points "0"

Points calculate based on prorate.

Example :

Group A:
100 % = 160 means then 90 % = 90%*160/100%

Answer :

Excel Workbook
	A	B	C
1	Client	Percentage	Points
2	A	40%	64
3	A	24%	0
4	A	125%	192
5	B	80%	64
6	B	49%	0
7	B	121%	96
8	C	76%	40.5308
9	C	74%	0
10	C	140%	64
11	D	75%	0
12	D	62%	-5
13	D	140%	53.33
Sheet2

 

[sahild05]

you could create a database (manually or with vba) to set up a lookup table with 100%, points, and lower limit (below which column C would have 0). This table would look like this:
Client Min% 100%
A 25% 160
B 50% 80
C 75% 53.33
D 70% 44.44

Then, to acommodate the negative points, you could add in a second condition based on a separate database.

Or you could write a piece of code that has all of this logic programmed in vba.

 

[vijaychennai]

Hello sahild05 I Need Formula....

That's Why i posted........

 

[sahild05]

assuming you set up the columns as described earlier in A-C, and column E contains the client and column F contains the percent, the formula for column G would be:

{=SUM(IF($A$1:$A$4=E4,1,0)*IF($B$1:$B$4<=F4,1,0)*$C$1:$C$4)*F4}

this is an array, so you'd have to hold shift+ctrl+Enter for it to work...

 

[kidwispa]

Copy the below formula into C2 and copy down - seems to work...


=IF(AND(A2="A",B2>0.25,B2<1.199),SUM((160/100)*B2*100),IF(AND(A2="A",B2<0.25),0,IF(AND(A2="A",B2>1.2),192,IF(AND(A2="B",B2>0.5,B2<1.199),SUM((80/100)*B2*100),IF(AND(A2="B",B2<0.5),0,IF(AND(A2="B",B2>1.2),96,IF(AND(A2="C",B2>0.75,B2<1.199),SUM((53.33/100)*B2*100),IF(AND(A2="C",B2<0.75),0,IF(AND(A2="C",B2>1.2),64,IF(AND(A2="D",B2>0.9,B2<1.199),SUM((44.44/100)*B2*100),IF(AND(A2="D",B2<0.7),"-5",IF(AND(A2="D",B2>0.7,B2<0.8999),0,IF(AND(A2="D",B2>1.2),53.33,0)))))))))))))

There may be a quicker way of doing this with VB but have just tried to use this with the data given and it returns the required answers