# Excel Grid Point Generation

1. ## 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  Register To Reply

2. ## 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.  Register To Reply

3. ## 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:  `Please Login or Register  to view this content.`

and for y
Formula:  `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  Register To Reply

4. ## 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  Register To Reply

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