Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: if question

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,060
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    Microsoft Excel - HtmlMaker1.10.xls
    File(F) Edit(E) View(V) Insert(I) Tool(T) Data(D) Window(W) Help(H)
    A1=Nums1
    ABCDEF
    1Nums1Nums2 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
    21225 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
    31035
    4850
    5465
    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •