+ Reply to Thread
Results 1 to 4 of 4

Excel Grid Point Generation

  1. #1
    Registered User
    Join Date
    07-25-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    7

    Excel Grid Point Generation

    Hi,
    I would like to request kind help from an excel expert.

    I would like to generate X and Y coordinate map based on the user inputs of "Starting X and Y coordinates" , "Ending X and Y" coordinates and the "X increments" and "Y increments"
    So basically the user would key-in all the above parameters and output is to be X and Y coordinates.

    Attached is an simple example did manually
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,336

    Re: Excel Grid Point Generation

    In many ways, this feels a lot like my permutation generator spreadsheet (https://www.excelforum.com/tips-and-...thout-vba.html ). Many would resort to VBA (where For..Next loops make this programming task easier), but I did not use VBA. In your spreadsheet, my solution looks like this:

    1) Starting point -- =A3 in A7 and =B3 in B7.
    2) We're incrementing Y before X, so B8 will include
    2a) B7+$F$3 when we want to increment or
    2b) $B$3 when we need to start over
    2c) an IF() function to choose between 2a and 2b when the previous Y is the ending coordinate. IF(B7>=$D$3,$B$3,B7+$F$3)
    2d) another IF() function to decide if we are done (and output NA() as our "done" placeholder. =IF(AND(A7>=$C$3,B7>=$D$3),NA(),IF(B7>=...)). Copy/paste/fill down farther than you will ever need. Note the mix of relative and absolute references for easy copying.
    3) In A8, we increment X whenever Y reaches its ending coordinate. This formula will have some similarities to Y, but with some differences.
    3a) When Y has not reached its upper limit, leave X unchanged -- A7
    3b) When Y has reached its upper limite, increment X A7+$E$3
    3c) And IF() function to choose between 3a and 3b when Y is at or above its maximum IF(B7>=$D$3,A7+$E$3,A7)
    3d) As with Y, we need an IF() function to tell us when we are done and output NA() when done. =IF(AND(A7>=$C$3,B7>=$D$3),NA(),IF(B7>=...)) and copy/paste/fill down farther than you will ever need. Again, note the mix of relative and absolute references.
    4) Edit the source data for your scatter plot to include the entire data range.

    With that in place, all you need to do is enter desired values in row 3 and your chart should follow suit. Again, I will note that this is often easier using a programming language that uses For..Next loops, so there is some value in considering programming language for this, but something like this should work, too.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Contributor
    Join Date
    10-14-2020
    Location
    Shanghai
    MS-Off Ver
    O365
    Posts
    152

    Re: Excel Grid Point Generation

    Hi,

    if you have O365 you can use the let formula to write an easy to understand formula

    For x coordinates
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    and for y
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I added this into the yellow field in the attachment.

    For the chart i used named range, then put the names (including the sheet name) to the value field.

    If you have no O365, you can use the formula and combine all into one, starting to replace the result line with the variable from above.

    good luck
    Attached Files Attached Files
    Last edited by hansolu; 05-02-2022 at 04:48 AM.

  4. #4
    Registered User
    Join Date
    07-25-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Excel Grid Point Generation

    Thank you very much MrShorty and hansolu for very detailed explainations on how to achieve this. I definitely learnt from both of you and I managed to achieve what I wanted to achieve

    Thank you both so much. I will mark this thread as solved.

    Best Regards

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 11-07-2019, 02:53 PM
  2. finding a point on a grid...
    By dowasson in forum Excel General
    Replies: 3
    Last Post: 09-19-2017, 01:14 PM
  3. [SOLVED] Grid ref at end point
    By WCE_123 in forum Excel General
    Replies: 4
    Last Post: 04-07-2014, 12:01 PM
  4. Extend grid point
    By WCE_123 in forum Excel General
    Replies: 2
    Last Post: 04-03-2014, 02:34 AM
  5. Extended Grid Ref Point
    By WCE_123 in forum Excel General
    Replies: 0
    Last Post: 03-06-2014, 07:11 AM
  6. Excel 2007 : Extracting a point in a grid
    By Weezy in forum Excel General
    Replies: 2
    Last Post: 02-17-2010, 11:28 AM
  7. I need to convert grid.doc to an Excel Grid.
    By suavejohn in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 09-01-2005, 02:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1