Help creating a 5x5 grid that solves for missing values

[VioletTesla]

I'm an after-school educational assistant working with young kids with a focus on math. If I can figure out how to get this to work in Excel (Using the 2016 version), I'll be using these puzzles on a yearly basis (switching from addition/subtraction to multiplication/division ect.) Anyway, the puzzles are similar to Magic Squares, where every row/column/diagonal equals the same value, but for my puzzles these r/c/d values are already known, and the values aren't all the same.

My goal at the end of this would be to have the kids create their own puzzles and pass them for others to solve, giving them an understanding on all sides of the subject, and to do that, I'd like for them to be able to use an Excel book that solves for a given puzzle. The only restrictions on the puzzles would be that each value can only be from Zero to Nine, but duplicate numbers can appear in a row/column/diagonal.

Hopefully this makes sense, below are a few tables I've tried to stitch together explaining a 4x4 puzzle:

The above demonstrates how a Excel should display the information, and below I've provided the beginnings of a puzzle with that display:

Here you can see that the solution to ^{A+4+C+9=27 [Row 1]} must work together with ^{A+8+0+M=22[Column 1]} , ^{C+G+K+9=23 [Column 3]} , and ^{A+F+K+P=23 [Diagonal 2]} .
One possible solution is listed below, and my goal is to code(? write?) an Excel sheet in a way that will solve for all of the previously blank values above (represented by letters).

The closest I've gotten is to have ^A+4+C+9=27 and then ^27-A+4+C+9 , which will give me a ¹⁴ , telling me that ^A+C=14 , but it doesn't solve for ^A or ^C when compared to ^{[Column 1]} or ^{[Column 3],} much less the entire grid. EXAMPLE:

Is it even possible to solve for this on Excel? Am I just too inexperienced with the program? I'd appreciate as much help as you can give, and if I can provide any more information let me know!! Thanks in advance!

 

[Eric W]

Welcome to the forum!

Yes, Excel can do this, but no, not with any of the basic tools. The closest you could get with a built-in tool is by using the Solver. It's possible to set up the parameters and constraints in Solver to solve this, but the amount of time and expertise necessary to do the set up would be daunting.

You'd need to write a program using VBA to solve this. I've written VBA programs to solve many types of number puzzles, including Sudoku, Kenken, Kakuro (Cross Numbers), Krypto, Magic Squares, and Challenger number puzzles. The Challenger puzzles are in fact almost the same as what you're looking for. Tweaking my program a tad and running it on your sample, I found that there are 4 possible answers to it, including:

	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
3					22						22						22						22
4	9	4	5	9	27		6	4	8	9	27		7	4	7	9	27		8	4	6	9	27
5	8	1	5	6	20		8	4	2	6	20		8	3	3	6	20		8	2	4	6	20
6	0	3	4	7	14		0	3	4	7	14		0	3	4	7	14		0	3	4	7	14
7	5	3	9	9	26		8	0	9	9	26		7	1	9	9	26		6	2	9	9	26
8	22	11	23	31	23		22	11	23	31	23		22	11	23	31	23		22	11	23	31	23

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

This would be easy enough to adapt for 5X5 puzzles as well. The part you mentioned that confuses me a bit is when you talk about addition/subtraction multiplication/division. If you mix operators in a puzzle, you start to worry about order of operations. And if you do subtraction or division, would you start at the top or left and just proceed in order?

Let me know and I'll see what I can do.