Enter numbers and x, y axis
Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: Enter numbers and x, y axis
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Nov 2007
    Location
    Moreno Valley, California
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Enter numbers and x, y axis

    Would you please be so kind as to show me how to create a formula in Cell F3 after entering any (x,y) coordinates from range B2:D4 and then it will automatically display the corresponding number in Cell F4?

    Also, vice versa, how to create a formula in Cell F7 and enter any numbers from range B6:D8 and automatically display the corresponding (x,y) coordinates in Cell F8?

    Please note: Using MS Office 365. The (x,y) coordinates in range B2:D4 corresponds to the numbers in range B6:D8

    I just made it 3x3 to make it easier
    to understand what I needed to be done. Thanks!

    Please view the following data:

    Range B2:D4
    [1,1] [0,1] [1,1] Example: x, y coordinates
    [-1,0] [0,0] [1,0] Cell F3 →[0,1] ENTER X,Y
    [-1,-1] [0,-1] [1,-1]Cell F4 →[7] RESULTS

    Range B6:D8
    [5] [7] [6] Example: numbers
    [8] [2] [3] Cell F7 →[0] ENTER #
    [0] [1] [7] Cell F8 →[-1,-1] RESULTS

  2. #2
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,228
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Enter numbers and x, y axis

    Try

    F4
    =INDEX(B6:D8,AGGREGATE(14,6,(ROW(B2:D4)-ROW(B2)+1)*(B2:D4=F3),1),AGGREGATE(14,6,(COLUMN(B2:D4)-COLUMN(B2)+1)*(B2:D4=F3),1))

    F8
    =INDEX(B2:D4,AGGREGATE(14,6,(ROW(B6:D8)-ROW(B6)+1)*(B6:D8=F7),1),AGGREGATE(14,6,(COLUMN(B6:D8)-COLUMN(B6)+1)*(B6:D8=F7),1))

    Hope this helps

    M.

  3. #3
    New Member
    Join Date
    Nov 2007
    Location
    Moreno Valley, California
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Enter numbers and x, y axis

    Thanks Marcelo!
    Wow! Very nice work! However, the formula in Cell F4 gives me an error when I enter [-1,1] [-1,0] and [-1,-1] in Cell F3. I did make an error on Cell B2, which should of been [-1,1] instead [1,1]. So this is an error on my part and not yours. So what must I do to revised the formula in Cell F4 to make it work correctly?
    Very appreciatively,
    Arthur

  4. #4
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,228
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Enter numbers and x, y axis

    I assumed you should enter only one value in F3, not multiple values: "when I enter [-1,1] [-1,0] and [-1,-1] in Cell F3"

    Could you clarify?

    M.

  5. #5
    New Member
    Join Date
    Nov 2007
    Location
    Moreno Valley, California
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Enter numbers and x, y axis

    It works good when I enter the coordinates 1,1 in Cell F3 and it displays the number 6 in Cell F4.

    But when I enter the coordinates -1,0 in Cell F3, it gives me an error in Cell F4 when it should display the number 8 instead but it does not.

    Formula in Cell F7 works great!!! This is where I do enter one value. I entered the number 7 in Cell F7 and it displays the coordinates 1,-1 in Cell F8.

  6. #6
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,228
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Enter numbers and x, y axis

    It worked perfectly for me


    A
    B
    C
    D
    E
    F
    1
    2
    [-1,1]
    [0,1]
    [1,1]
    3
    [-1,0]
    [0,0]
    [1,0]
    [-1,0]
    4
    [-1,-1]
    [0,1]
    [1,-1]
    [8]
    5
    6
    [5]
    [7]
    [6]
    7
    [8]
    [2]
    [3]
    8
    [0]
    [1]
    [7]
    9


    Formula in F4 (see post 2)

    If it's not working for you, check if the cell F3 is equal to B3 - try in an empty cell
    =B3=F3

    If this return FALSE probably there is an extraneous space either in B3 or in F3

    M.

  7. #7
    New Member
    Join Date
    Nov 2007
    Location
    Moreno Valley, California
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Enter numbers and x, y axis

    I did not see any extraneous spaces in Cells B3 or in F3. I did received the FALSE when I typed =B3=F3. I tried all the other cells and I got the same FALSE too but they worked! I even created a new spreadsheet and I still got the errors if I entered either of these three coordinates: (-1,0) (-1,1) or (-1,-1). It looks like it will not accept the first negative numbers on cells B2, B3, B4 in Cell F3. I don't know why. I will not display any of these numbers: 5, 8,or 0. I only get an error dialog box. I was analyzing both formulas and the only thing I did not understand is that what does the numbers 14,6 mean? I hope I can find out what is wrong.

  8. #8
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,228
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Enter numbers and x, y axis

    You should enter [-1,0] that is: using square brackets, not (-1,0)

    M.


  9. #9
    New Member
    Join Date
    Nov 2007
    Location
    Moreno Valley, California
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Enter numbers and x, y axis

    It works perfect!!! I was not entering the apostrophe ‘ before -1,0. I don’t have to enter the parentheses ( ) nor the brackets [ ] for it to work. It worked all along. But it’s funny that if the coordinates first digits are negative like -1,1 or -1,-1 or -1,0 and “without” typing the apostrophe ‘ in front, it will not work. Thank you so much. I apologized for any inconvenience. It works beautifully!!!

  10. #10
    New Member
    Join Date
    Nov 2007
    Location
    Moreno Valley, California
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Enter numbers and x, y axis

    But what does the 14,6 means in the formula?? I still would like to know.

Some videos you may like

User Tag List

Tags for this Thread

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
  •