One More Excel Formula Help....

[vijaychennai]

Hello all,

I have data like this...

Excel Workbook
	A	B
1	Data	Condition
2	85%	a
3	60%	b
4	125%	a
5	91%	a
6	85%	b
7	60%	b
Sheet3

Weightage.

Excel Workbook
	A	B	C	D	E
1	A			B
2	Weightage	Point		Weightage	Point
3	0-69.99%	-5		0-59.99	10
4	70% to 89.99%	0		60 to 89.99	20
5	90% to 94.99%	30		90% to 94.99%	30
6	95% to 99.99%	40		95% to 99.99%	40
7	100 % to 109.99%	50		100 % to 109.99%	50
8	More than 110 %	60		More than 110 %	60
Sheet1

A & B Weightage will defer.

Answer :

Excel Workbook
	A	B	C
1	Data	Condition	Point
2	85%	a	0
3	60%	b	20
4	125%	a	60
5	91%	a	30
6	85%	b	20
7	60%	b	10
Sheet3

 

[tushiroda]

a few things, you need the ranges in numeric format to make them usable. I've broken it out here instead of having the formula parse it.

also, the last result in your example looks off as it should be 20. the formula isn't super robust, but you should be able to adjust it depending on how/what you need.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Data	Condition	Point		A				B
2	85%	a	0		Weightage		Point		Weightage	Point
3	60%	b	20		0-69.99%	0	-5	0	0-59.99	10
4	125%	a	60		70% to 89.99%	0.7	0	0.6	60 to 89.99	20
5	91%	a	30		90% to 94.99%	0.9	30	0.9	90% to 94.99%	30
6	85%	b	20		95% to 99.99%	0.95	40	0.95	95% to 99.99%	40
7	60%	b	20		100 % to 109.99%	1	50	1	100 % to 109.99%	50
8					More than 110 %	1.1	60	1.1	More than 110 %	60
Sheet1

Excel 2007

Cell Formulas
Range	Formula
C2	=IF(B2="a",INDEX($G$3:$G$8,SUMPRODUCT(--($F$3:$F$8<=A2))),INDEX($J$3:$J$8,SUMPRODUCT(--($H$3:$H$8<=A2))))

 

[vijaychennai]

Hello all,

It's Not working....

Please help!!!

 

[mancemonster]

If you replaced

0-69.99%
70-89.99%
90-94.99%
95-99.99%
100-109.99%
110%+

With..

Table A

0%
70%
90%
95%
100%
110%

Likewise with table B

0%
60%
90%
95%
100%
110%

you could use something like..

=IF(F17="a",INDEX($E$6:$E$11,MATCH(E17,$C$6:$C$11,1),0,1),IF(F17="b",INDEX($I$6:$I$11,MATCH(E17,$G$6:$G$11,1),0,1),""))

This will pick the value you require if a or b , and will return "" if neither apply.

Regards


Roger