qtr formula

[sachin483]

if all 4 qtr is >= 100 then 20000 , if any 3 qtr > 100 and anl > 105 ach% , then 20000 after matching col1 and col2 with the grid

[TABLE="width: 341"]
<tbody>[TR]
[TD]grid[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]col1[/TD]
[TD]AA[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]ANL[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]XX[/TD]
[TD]25000[/TD]
[TD]20000[/TD]
[TD]16500[/TD]
[TD]13000[/TD]
[TD]105[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]YY[/TD]
[TD]20000[/TD]
[TD]17000[/TD]
[TD]14500[/TD]
[TD]12000[/TD]
[TD]110[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]data[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]col1[/TD]
[TD]col2[/TD]
[TD]QTR1[/TD]
[TD]QTR2[/TD]
[TD]QTR3[/TD]
[TD]QTR4[/TD]
[TD]ANL[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]XX[/TD]
[TD]A[/TD]
[TD]84[/TD]
[TD]124[/TD]
[TD]111[/TD]
[TD]108[/TD]
[TD]106[/TD]
[TD]20000[/TD]
[/TR]
[TR]
[TD]XX[/TD]
[TD]A[/TD]
[TD]108[/TD]
[TD]101[/TD]
[TD]113[/TD]
[TD]109[/TD]
[TD]108[/TD]
[TD]20000[/TD]
[/TR]
[TR]
[TD]XX[/TD]
[TD]A[/TD]
[TD]104[/TD]
[TD]112[/TD]
[TD]103[/TD]
[TD]109[/TD]
[TD]107[/TD]
[TD]20000[/TD]
[/TR]
[TR]
[TD]XX[/TD]
[TD]A[/TD]
[TD]104[/TD]
[TD]105[/TD]
[TD]109[/TD]
[TD]85[/TD]
[TD]101[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]XX[/TD]
[TD]AA[/TD]
[TD]93[/TD]
[TD]133[/TD]
[TD]110[/TD]
[TD]110[/TD]
[TD]111[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]XX[/TD]
[TD]AA[/TD]
[TD]100[/TD]
[TD]122[/TD]
[TD]112[/TD]
[TD]94[/TD]
[TD]107[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]XX[/TD]
[TD]B[/TD]
[TD]68[/TD]
[TD]133[/TD]
[TD]114[/TD]
[TD]115[/TD]
[TD]107[/TD]
[TD]16500[/TD]
[/TR]
[TR]
[TD]XX[/TD]
[TD]B[/TD]
[TD]101[/TD]
[TD]114[/TD]
[TD]108[/TD]
[TD]105[/TD]
[TD]107[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]XX[/TD]
[TD]C[/TD]
[TD]103[/TD]
[TD]117[/TD]
[TD]118[/TD]
[TD]105[/TD]
[TD]111[/TD]
[TD]13000[/TD]
[/TR]
[TR]
[TD]XX[/TD]
[TD]C[/TD]
[TD]85[/TD]
[TD]119[/TD]
[TD]115[/TD]
[TD]112[/TD]
[TD]108[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]YY[/TD]
[TD]A[/TD]
[TD]85[/TD]
[TD]125[/TD]
[TD]112[/TD]
[TD]109[/TD]
[TD]108[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]YY[/TD]
[TD]A[/TD]
[TD]109[/TD]
[TD]102[/TD]
[TD]114[/TD]
[TD]110[/TD]
[TD]109[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]YY[/TD]
[TD]A[/TD]
[TD]105[/TD]
[TD]113[/TD]
[TD]104[/TD]
[TD]110[/TD]
[TD]108[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]YY[/TD]
[TD]A[/TD]
[TD]100[/TD]
[TD]119[/TD]
[TD]127[/TD]
[TD]86[/TD]
[TD]108[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]YY[/TD]
[TD]AA[/TD]
[TD]94[/TD]
[TD]134[/TD]
[TD]111[/TD]
[TD]111[/TD]
[TD]113[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]YY[/TD]
[TD]AA[/TD]
[TD]101[/TD]
[TD]123[/TD]
[TD]113[/TD]
[TD]95[/TD]
[TD]108[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]YY[/TD]
[TD]B[/TD]
[TD]69[/TD]
[TD]134[/TD]
[TD]115[/TD]
[TD]116[/TD]
[TD]109[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]YY[/TD]
[TD]B[/TD]
[TD]102[/TD]
[TD]115[/TD]
[TD]109[/TD]
[TD]106[/TD]
[TD]108[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]YY[/TD]
[TD]C[/TD]
[TD]104[/TD]
[TD]118[/TD]
[TD]119[/TD]
[TD]106[/TD]
[TD]112[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]YY[/TD]
[TD]C[/TD]
[TD]86[/TD]
[TD]120[/TD]
[TD]116[/TD]
[TD]113[/TD]
[TD]109[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[njimack]

Not entirely sure that I've understood your request but see if this is what you need..

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	col1	col2	QTR1	QTR2	QTR3	QTR4	ANL	Amount		col1	AA	A	B	C	ANL
2	XX	A	84	124	111	108	106	,">=100")=4,20000,IF(AND(COUNTIF(Table1:],">=100")=3,>105),INDEX(Table2,MATCH(,Table2,0),MATCH(,Table2,0)),0))]20000		XX	25000	20000	16500	13000	105
3	XX	A	108	101	113	109	108	,">=100")=4,20000,IF(AND(COUNTIF(Table1:],">=100")=3,>105),INDEX(Table2,MATCH(,Table2,0),MATCH(,Table2,0)),0))]20000		YY	20000	17000	14500	12000	110
4	XX	A	104	112	103	109	107	,">=100")=4,20000,IF(AND(COUNTIF(Table1:],">=100")=3,>105),INDEX(Table2,MATCH(,Table2,0),MATCH(,Table2,0)),0))]20000
5	XX	A	104	105	109	85	101	,">=100")=4,20000,IF(AND(COUNTIF(Table1:],">=100")=3,>105),INDEX(Table2,MATCH(,Table2,0),MATCH(,Table2,0)),0))]0
6	XX	AA	93	133	110	110	111	,">=100")=4,20000,IF(AND(COUNTIF(Table1:],">=100")=3,>105),INDEX(Table2,MATCH(,Table2,0),MATCH(,Table2,0)),0))]25000
7	XX	AA	100	122	112	94	107	,">=100")=4,20000,IF(AND(COUNTIF(Table1:],">=100")=3,>105),INDEX(Table2,MATCH(,Table2,0),MATCH(,Table2,0)),0))]25000
8	XX	B	68	133	114	115	107	,">=100")=4,20000,IF(AND(COUNTIF(Table1:],">=100")=3,>105),INDEX(Table2,MATCH(,Table2,0),MATCH(,Table2,0)),0))]16500
9	XX	B	101	114	108	105	107	,">=100")=4,20000,IF(AND(COUNTIF(Table1:],">=100")=3,>105),INDEX(Table2,MATCH(,Table2,0),MATCH(,Table2,0)),0))]20000
10	XX	C	103	117	118	105	111	,">=100")=4,20000,IF(AND(COUNTIF(Table1:],">=100")=3,>105),INDEX(Table2,MATCH(,Table2,0),MATCH(,Table2,0)),0))]20000
11	XX	C	85	119	115	112	108	,">=100")=4,20000,IF(AND(COUNTIF(Table1:],">=100")=3,>105),INDEX(Table2,MATCH(,Table2,0),MATCH(,Table2,0)),0))]13000
12	YY	A	85	125	112	109	108	,">=100")=4,20000,IF(AND(COUNTIF(Table1:],">=100")=3,>105),INDEX(Table2,MATCH(,Table2,0),MATCH(,Table2,0)),0))]17000
13	YY	A	109	102	114	110	109	,">=100")=4,20000,IF(AND(COUNTIF(Table1:],">=100")=3,>105),INDEX(Table2,MATCH(,Table2,0),MATCH(,Table2,0)),0))]20000
14	YY	A	105	113	104	110	108	,">=100")=4,20000,IF(AND(COUNTIF(Table1:],">=100")=3,>105),INDEX(Table2,MATCH(,Table2,0),MATCH(,Table2,0)),0))]20000
15	YY	A	100	119	127	86	108	,">=100")=4,20000,IF(AND(COUNTIF(Table1:],">=100")=3,>105),INDEX(Table2,MATCH(,Table2,0),MATCH(,Table2,0)),0))]17000
16	YY	AA	94	134	111	111	113	,">=100")=4,20000,IF(AND(COUNTIF(Table1:],">=100")=3,>105),INDEX(Table2,MATCH(,Table2,0),MATCH(,Table2,0)),0))]20000
17	YY	AA	101	123	113	95	108	,">=100")=4,20000,IF(AND(COUNTIF(Table1:],">=100")=3,>105),INDEX(Table2,MATCH(,Table2,0),MATCH(,Table2,0)),0))]20000
18	YY	B	69	134	115	116	109	,">=100")=4,20000,IF(AND(COUNTIF(Table1:],">=100")=3,>105),INDEX(Table2,MATCH(,Table2,0),MATCH(,Table2,0)),0))]14500
19	YY	B	102	115	109	106	108	,">=100")=4,20000,IF(AND(COUNTIF(Table1:],">=100")=3,>105),INDEX(Table2,MATCH(,Table2,0),MATCH(,Table2,0)),0))]20000
20	YY	C	104	118	119	106	112	,">=100")=4,20000,IF(AND(COUNTIF(Table1:],">=100")=3,>105),INDEX(Table2,MATCH(,Table2,0),MATCH(,Table2,0)),0))]20000
21	YY	C	86	120	116	113	109	,">=100")=4,20000,IF(AND(COUNTIF(Table1:],">=100")=3,>105),INDEX(Table2,MATCH(,Table2,0),MATCH(,Table2,0)),0))]12000
Sheet2

 

[sachin483]

[TABLE="width: 512"]
<tbody>[TR]
[TD="class: xl65, width: 64"]grid[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl63"]col1[/TD]
[TD="class: xl66"]AA[/TD]
[TD="class: xl66"]A[/TD]
[TD="class: xl66"]B[/TD]
[TD="class: xl66"]C[/TD]
[TD="class: xl63"]ANL[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl68"]XX[/TD]
[TD="class: xl63"]25000[/TD]
[TD="class: xl63"]20000[/TD]
[TD="class: xl63"]16500[/TD]
[TD="class: xl63"]13000[/TD]
[TD="class: xl63"]105[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]data[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl63"]col1[/TD]
[TD="class: xl64"]col2[/TD]
[TD="class: xl64"]QTR1[/TD]
[TD="class: xl64"]QTR2[/TD]
[TD="class: xl64"]QTR3[/TD]
[TD="class: xl64"]QTR4[/TD]
[TD="class: xl64"]ANL[/TD]
[TD="class: xl64"]amount[/TD]
[/TR]
[TR]
[TD="class: xl68"]XX[/TD]
[TD="class: xl67"]A[/TD]
[TD="class: xl64"]84[/TD]
[TD="class: xl69"]124[/TD]
[TD="class: xl69"]111[/TD]
[TD="class: xl69"]108[/TD]
[TD="class: xl69"]106[/TD]
[TD="class: xl65"][/TD]
[/TR]
</tbody>[/TABLE]

i want to first compare col1 of data with the grid of col1 then data col2 with transpose (AA,A,B,C) of grid and then check that all the qtr are >=100 of data , if 100 then the amount as per transpose slab(AA,A,B,C) and then any 3 qtr >= 100 of data and anl of data greater than anl of grid then the amount from transpose slab id (AA,A,B,C)

the formula i have tried but reflecting error at table1 as i given name range to table1 to data and table2 to grid

 

[njimack]

Like this?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	col1	col2	QTR1	QTR2	QTR3	QTR4	ANL	Amount		col1	AA	A	B	C	ANL
2	XX	A	84	124	111	108	106	,">100")=4,AND(COUNTIF(Table1:],">100")=3,>=INDEX(Table2,MATCH(,Table2,0)))),INDEX(Table2,MATCH(,Table2,0),MATCH(,Table2,0)),"")]20000		XX	25000	20000	16500	13000	105
3	XX	A	108	101	113	109	108	,">100")=4,AND(COUNTIF(Table1:],">100")=3,>=INDEX(Table2,MATCH(,Table2,0)))),INDEX(Table2,MATCH(,Table2,0),MATCH(,Table2,0)),"")]20000		YY	20000	17000	14500	12000	110
4	XX	A	104	112	103	109	107	,">100")=4,AND(COUNTIF(Table1:],">100")=3,>=INDEX(Table2,MATCH(,Table2,0)))),INDEX(Table2,MATCH(,Table2,0),MATCH(,Table2,0)),"")]20000
5	XX	A	104	105	109	85	101	,">100")=4,AND(COUNTIF(Table1:],">100")=3,>=INDEX(Table2,MATCH(,Table2,0)))),INDEX(Table2,MATCH(,Table2,0),MATCH(,Table2,0)),"")]
6	XX	AA	93	133	110	110	111	,">100")=4,AND(COUNTIF(Table1:],">100")=3,>=INDEX(Table2,MATCH(,Table2,0)))),INDEX(Table2,MATCH(,Table2,0),MATCH(,Table2,0)),"")]25000
7	XX	AA	100	122	112	94	107	,">100")=4,AND(COUNTIF(Table1:],">100")=3,>=INDEX(Table2,MATCH(,Table2,0)))),INDEX(Table2,MATCH(,Table2,0),MATCH(,Table2,0)),"")]
8	XX	B	68	133	114	115	107	,">100")=4,AND(COUNTIF(Table1:],">100")=3,>=INDEX(Table2,MATCH(,Table2,0)))),INDEX(Table2,MATCH(,Table2,0),MATCH(,Table2,0)),"")]16500
9	XX	B	101	114	108	105	107	,">100")=4,AND(COUNTIF(Table1:],">100")=3,>=INDEX(Table2,MATCH(,Table2,0)))),INDEX(Table2,MATCH(,Table2,0),MATCH(,Table2,0)),"")]16500
10	XX	C	103	117	118	105	111	,">100")=4,AND(COUNTIF(Table1:],">100")=3,>=INDEX(Table2,MATCH(,Table2,0)))),INDEX(Table2,MATCH(,Table2,0),MATCH(,Table2,0)),"")]13000
11	XX	C	85	119	115	112	108	,">100")=4,AND(COUNTIF(Table1:],">100")=3,>=INDEX(Table2,MATCH(,Table2,0)))),INDEX(Table2,MATCH(,Table2,0),MATCH(,Table2,0)),"")]13000
12	YY	A	85	125	112	109	108	,">100")=4,AND(COUNTIF(Table1:],">100")=3,>=INDEX(Table2,MATCH(,Table2,0)))),INDEX(Table2,MATCH(,Table2,0),MATCH(,Table2,0)),"")]
13	YY	A	109	102	114	110	109	,">100")=4,AND(COUNTIF(Table1:],">100")=3,>=INDEX(Table2,MATCH(,Table2,0)))),INDEX(Table2,MATCH(,Table2,0),MATCH(,Table2,0)),"")]17000
14	YY	A	105	113	104	110	108	,">100")=4,AND(COUNTIF(Table1:],">100")=3,>=INDEX(Table2,MATCH(,Table2,0)))),INDEX(Table2,MATCH(,Table2,0),MATCH(,Table2,0)),"")]17000
15	YY	A	100	119	127	86	108	,">100")=4,AND(COUNTIF(Table1:],">100")=3,>=INDEX(Table2,MATCH(,Table2,0)))),INDEX(Table2,MATCH(,Table2,0),MATCH(,Table2,0)),"")]
16	YY	AA	94	134	111	111	113	,">100")=4,AND(COUNTIF(Table1:],">100")=3,>=INDEX(Table2,MATCH(,Table2,0)))),INDEX(Table2,MATCH(,Table2,0),MATCH(,Table2,0)),"")]20000
17	YY	AA	101	123	113	95	108	,">100")=4,AND(COUNTIF(Table1:],">100")=3,>=INDEX(Table2,MATCH(,Table2,0)))),INDEX(Table2,MATCH(,Table2,0),MATCH(,Table2,0)),"")]
18	YY	B	69	134	115	116	109	,">100")=4,AND(COUNTIF(Table1:],">100")=3,>=INDEX(Table2,MATCH(,Table2,0)))),INDEX(Table2,MATCH(,Table2,0),MATCH(,Table2,0)),"")]
19	YY	B	102	115	109	106	108	,">100")=4,AND(COUNTIF(Table1:],">100")=3,>=INDEX(Table2,MATCH(,Table2,0)))),INDEX(Table2,MATCH(,Table2,0),MATCH(,Table2,0)),"")]14500
20	YY	C	104	118	119	106	112	,">100")=4,AND(COUNTIF(Table1:],">100")=3,>=INDEX(Table2,MATCH(,Table2,0)))),INDEX(Table2,MATCH(,Table2,0),MATCH(,Table2,0)),"")]12000
21	YY	C	86	120	116	113	109	,">100")=4,AND(COUNTIF(Table1:],">100")=3,>=INDEX(Table2,MATCH(,Table2,0)))),INDEX(Table2,MATCH(,Table2,0),MATCH(,Table2,0)),"")]
Sheet2

 

[sachin483]

i am using excel 2010 & 2007 error message reflecting while entering the formula at -- Table1[@[QTR1]

 

[njimack]

My post clearly shows that it works. Have you created a Table called Table 1?

 

[Peter_SSs]

@njimack
Neil, not sure if you are aware that you can use the ‘Analyse range (Forum)’ field near the top left of the Excel jeanie screen to restrict the number of formulas generated. Click or click & drag if there is a single contiguous region of formulas you want to display. If non-contiguous cells, just type the cell addresses with commas between.

There is generally no need to display multiple formulas that are basically the same, it just fills up the board and makes your post and the thread hard to read/navigate.

 

[sachin483]

yes i have created Table1 as data and Table2 as Grid

 

[njimack]

What's the error message?

 

[sachin483]

thanks a lot @njimack now working fine i was not inserting the table through insert command but giving the range name from the left corner both are different can you explained a little bit how its work