if question

[dkinnison]

14 20
12 25
10 35
8 50
4 65

I HAVE A CELL AT LET SAY E5 = TO 30
THE NEXT NUMBER UP IS 35 AND I WANT
TO DISPLAY THE 10.

IF E5 = TO 55 THAN I WANT IT TO DISPLAY
4

WHAT IS THE FORMULA FOR THIS?

MY EMAIL IS dkinniso@dyag-eng.com
or dkinnison@voyager.net

Thanks


Daniel Kinnison
Dyag Automation Group
10489 Grand River, Suite E
Brighton, Mich. 48116
PH: 810.229.7900 EX:114

[Aladin Akyurek]

Microsoft Excel - HtmlMaker1.10.xls
File(F) Edit(E) View(V) Insert(I) Tool(T) Data(D) Window(W) Help(H)
A1		=	Nums1
	A	B	C	D	E	F
1	Nums1	Nums2		30	:alert('=OFFSET($A$2,IF(COUNTIF(B$1:B$5,D1),MATCH(D1,B$1:B$5,0),MATCH(D1,B$1:B$5))-1,0,1,1)')>10	:alert('=INDEX(A$1:A$5,IF(COUNTIF(B$1:B$5,D1),MATCH(D1,B$1:B$5,0),MATCH(D1,B$1:B$5)+1))')>10
2	12	25		55	:alert('=OFFSET($A$2,IF(COUNTIF(B$1:B$5,D2),MATCH(D2,B$1:B$5,0),MATCH(D2,B$1:B$5))-1,0,1,1)')>4	:alert('=INDEX(A$1:A$5,IF(COUNTIF(B$1:B$5,D2),MATCH(D2,B$1:B$5,0),MATCH(D2,B$1:B$5)+1))')>4
3	10	35
4	8	50
5	4	65
Sheet1

You can see the formula of cells only click each above hyperlinks

The above image was automatically written by excel VBA. If you want this code, click here and I'll email the file to you.


Here are 2 sets of formulas to achieve the retrieval task that you formulated:

In E1:

=OFFSET($A$2,IF(COUNTIF(B$1:B$5,D1),MATCH(D1,B$1:B$5,0),MATCH(D1,B$1:B$5))-1,0,1,1)

In F1:

=INDEX(A$1:A$5,IF(COUNTIF(B$1:B$5,D1),MATCH(D1,B$1:B$5,0),MATCH(D1,B$1:B$5)+1))

which also can be seen by activating the hyperlinks in these cells [ Created by HtmlMaker, designed by Colo. My deeply felt thanks for that ].


Aladin


[ This Message was edited by: Aladin Akyurek on 2002-05-10 05:02 ]

[ This Message was edited by: Aladin Akyurek on 2002-05-10 08:03 ]