Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 05-05-2019, 08:26 PM
arthurz11 arthurz11 is offline Windows 10 Office 2013
Novice
 
Join Date: Dec 2017
Posts: 6
arthurz11 is on a distinguished road
Default Enter x, y coordinates and displays a number

Range B2-D4:
[1,1] [0,1] [1,1] Example:
[-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:
[8] [2] [3] Cell F7 → [0] (ENTER #)
[0] [1] [7] Cell F8 → [-1,-1] (RESULTS)

Would you please be so kind as to show me how to
create a formula after entering any (x,y) coordinates
in Cell F3, it will automatically display the corresponding number in Cell F4?
Also, vice versa, where I would enter any number in
Cell F7 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 matrix to make it easier
to understand what I needed to be done. Thanks!
Reply With Quote
  #2  
Old 05-11-2019, 10:37 AM
p45cal p45cal is offline Windows 10 Office 2016
Expert
 
Join Date: Apr 2014
Posts: 253
p45cal has a spectacular aura aboutp45cal has a spectacular aura aboutp45cal has a spectacular aura about
Default

In F4:
Code:
=INDEX(B6:D8,SUMPRODUCT((B2:D4=F3)*ROW(B2:D4))-ROW(B2:D4)+1,SUMPRODUCT((B2:D4=F3)*COLUMN(B2:D4))-COLUMN(B2:D4)+1)
In F8:
Code:
=INDEX(B2:D4,SUMPRODUCT((B6:D8=F7)*ROW(B6:D8))-ROW(B6:D8)+1,SUMPRODUCT((B6:D8=F7)*COLUMN(B6:D8))-COLUMN(B6:D8)+1)
BOTH Array-Entered, ie. use Ctrl+Shift+Enter to commit the formulae to the sheet and not just Enter.


Reply With Quote
  #3  
Old 05-21-2019, 11:52 AM
arthurz11 arthurz11 is offline Windows 10 Office 2013
Novice
 
Join Date: Dec 2017
Posts: 6
arthurz11 is on a distinguished road
Default enter x,y to display a number

I had to revised my data. I have to create the problem differently because I need to use two cells for x,y coordinates instead of one cell as you previously helped. The objective is to enter the x,y coordinates in cells H2 and I2 and it displays the corresponding value 7 in cell I4. This is the following DATA:

Range: A1:F3 graph:
cell A1 is -1---cell B1 is 1---cell C1 is 0---cell D1 is 1---cell E1 is 1---cell F1 is 1
cell A2 is -1---cell B2 is 0---cell C2 is 0---cell D2 is 1---cell E2 is 1---cell F2 is 0
cell A3 is -1---cell B3 is -1---cell C3 is 0---cell D3 is -1---cell E3 is 1---cell F3 is -11

Range: A5:C7
cell A5 is 1---cell B5 is 2---cell C5 is 3
cell A6 is 4---cell B6 is 5---cell C6 is 6
cell A7 is 7---cell B7 is 8---cell C7 is 9

In cell H2 I enter the -1 and in cell I2 I enter -1 (This is the x,y coordinates that I entered)

In cell I4 the formula finds the number and displays the 7
1 2 3
4 5 6
7 8 9

I want to input the x,y coordinates in two cells H2 and I2 in stead of one as previously. So on this one I have cells H2 and I2.
The formula in cell I2 automatically puts the assigned perspective value.
Reply With Quote
  #4  
Old 05-21-2019, 12:59 PM
p45cal p45cal is offline Windows 10 Office 2016
Expert
 
Join Date: Apr 2014
Posts: 253
p45cal has a spectacular aura aboutp45cal has a spectacular aura aboutp45cal has a spectacular aura about
Default

in cell I4, array-enter:
Code:
=MIN(IF((($A$1:$C$3=$H$2)*($D$1:$F$3=$I$2))=1,$A$5:$C$7))
It returns the smallest result if there are more than one (eg. if x and y were both 1).


edit post posting: It's probably wrong.
Try instead, still array-entered:
Code:
=MIN(IF((CHOOSE({1,2,3},$A$1:$A$3,$C$1:$C$3,$E$1:$E$3)=$H$2)*(CHOOSE({1,2,3},$B$1:$B$3,$D$1:$D$3,$F$1:$F$3)=$I$2)=1,$A$5:$C$7))
Reply With Quote
  #5  
Old 05-21-2019, 01:01 PM
arthurz11 arthurz11 is offline Windows 10 Office 2013
Novice
 
Join Date: Dec 2017
Posts: 6
arthurz11 is on a distinguished road
Default enter x,y to display a number

My mind feels like there's a ball bouncing back and forth inside my head. Correction on data. Sorry for the errors. Cell D1 is 0 and Cell F3 is -1.
Reply With Quote
  #6  
Old 05-21-2019, 01:13 PM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 685
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

Cross-posted at: https://www.mrexcel.com/forum/excel-...-x-y-axis.html
Reply With Quote
  #7  
Old 05-21-2019, 01:48 PM
p45cal p45cal is offline Windows 10 Office 2016
Expert
 
Join Date: Apr 2014
Posts: 253
p45cal has a spectacular aura aboutp45cal has a spectacular aura aboutp45cal has a spectacular aura about
Default

updated response in msg#4
Reply With Quote
  #8  
Old 05-21-2019, 02:20 PM
arthurz11 arthurz11 is offline Windows 10 Office 2013
Novice
 
Join Date: Dec 2017
Posts: 6
arthurz11 is on a distinguished road
Default

If I enter the values for H2 and I2 these are my results:
H2,I2
-1,-1 Correct! 1
-1,0 Correct! 4
-1,-1 Correct! 7
0,1 Incorrect 3, Answer should be 2
0,0 Incorrect 6, Answer should be 5
0,-1 Incorrect 9, Answer should be 8
1,1 Incorrect 2, Answer should be 3
1,0 Incorrect 0, Answer should be 6
1,-1 Incorrect 0, Answer should be 9

I tried revising the formula but I couldn't figure it out. Would you please be so kind as to show me what can I do to the current formula so it will work?
Reply With Quote
  #9  
Old 05-21-2019, 02:26 PM
arthurz11 arthurz11 is offline Windows 10 Office 2013
Novice
 
Join Date: Dec 2017
Posts: 6
arthurz11 is on a distinguished road
Default

I used your updated formula! You are utterly AWESOME!! It worked beautifully. Now, I can go to sleep. Thanks a million!!!
Reply With Quote
  #10  
Old 05-21-2019, 03:22 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,390
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

arthurz11: For cross-posting etiquette, please read http://www.excelguru.ca/content.php?184
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
xref to heading 5.1 stubbornly displays 0 as heading number eNGiNe Word 4 06-17-2018 11:05 PM
enter a constant based on entered number So New2 This Word VBA 1 10-17-2014 05:29 AM
Enter a number in a locked equation within a cell that then calculates Carchee Excel 7 06-13-2014 11:11 AM
VBA code to read number of footnote and enter in text rekent Word VBA 2 05-13-2014 06:53 AM
Enter Number on any sheet one time only. paulrm906 Excel 1 04-28-2006 07:35 AM


All times are GMT -7. The time now is 10:05 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft