How do I assign letters from a word to other words for arithmetic operations on Mac mini?

Here is my take on it.

Table 1:

Top two rows are header rows. All is typed in, no formulas.

Column A words are typed in

B3 =XLOOKUP(MID($A3,COLUMN()−1,1),$1:$1,$2:$2,"",0)

Fill down to bottom of table

Fill all of those across to the second to last column

N3 (last column) =VALUE(0&CONCAT(B3:M3))

Fill down to bottom of table

There is no error checking to make sure the words you typed use only the letters of your code word. You will either see gaps (empty cells where there should be numbers) or there will not be enough numbers for the word if a letter was not in the code word.

Table 2

Columns A and C are your typed in words

Column B you type one of the math operators + - * /

D2 =XLOOKUP(A2,Table 1::$A,Table 1::$N,"",0)

E2 =XLOOKUP(C2,Table 1::$A,Table 1::$N,"",0)

F2 =SWITCH(B2,"+",D2+E2,"-",D2−E2,"*",D2×E2,"/",D2÷E2,"")

Fill down with all three to the bottom of the table.

You can hide columns D and E.

What a great exercise.

At first I thought this was easy - a simple lookup() (or XLOOKUP()) using two tables - one for the Master word, and one for the calculations:

The formula in Puzzle::A3 is:

=XLOOKUP(A1,MASTER::1:1,MASTER::2:2,0,0,1)+
XLOOKUP(A2,MASTER::1:1,MASTER::2:2,0,0,1)

Breaking this down, it's a series of two XLOOKUPS, where:

=XLOOKUP(A1,MASTER::1:1,MASTER::2:2,0,0,1)

takes the value in cell A1 (blank in this case) and looks it up in Master::1:1 (row 1 of the Master table). For any match, it returns the corresponding value in Master::2:2 (row 2 of the Master table).

If no match is found it returns 0 - if you don't need this control, you could use a simpler LOOKUP() that will throw an error when no match is found, which might be useful to make sure the calculation is valid (i.e. only uses valid letters_.

Either way, this returns the corresponding 'score' for the given letter.

This result is added to another similar calculation that works on the second word (A2).

Copied across, this gives you the calculation that works out each letter pairing.

Then comes the gotcha. In the case of A + L we get a result of 12, so that needs to be carried over, and requires an extension of the formula.

To do this, I added another row that takes these values and gets the answers you're looking for:

Cell A4:

=MOD(A3+QUOTIENT(B3,10),10)

QUOTIENT() works out the 'carry over' value from B3 ÷ 10 and adds that to A3 (0 in this case), then divides by 10 again to get the remainder. Filling this across, and editing H4 (which doesn't have a QUOTIENT() to calculate) gets:

Now Puzzle::row 3 can be hidden since it's not needed and helps clean up the sheet. You can also use conditional formatting to hide the 0 values.

What isn't clear is the addition/subtraction piece - are they always in this order? In which case you can duplicate the formulas and just replace + with -.

If you want a more dynamic approach, where you can enter + or - in a field and have it filter through, that's a little more work, but still doable.

OK, I've revised my sheet and simplified it.

I realized my addition logic was overcomplicated with all that MOD() and QUOTIENT() stuff, and it didn't work for subtraction (or, at least, the logic is far more complicated that way around)

So now I have three tables:

The MASTER table holds the key word and letter scores.

Candidate words are entered in the WORD LIST table. For each word, that word's score is calculated, along with the addition/subtraction calculation (based on word1 + word2; word3+word4; word5-word6

The magic happens in the PUZZLE table where the words are broken out into individual letters, their respective values are calculated, and the word scores computed. These scores are then copied back to the Word List table.

The only place you need to edit is the WORD LIST table (unless you want to change the rules/logic/limits).

Here's a link to the spreadsheet you can download: https://drive.google.com/file/d/1sdP0Fwms9rt1AYnz7WO0s3OcVmsEdcJf/view?usp=share_link

Oh, there's no error checking for invalid words. That's currently still on you to make sure the words match the master.

Second take on it where you don't need to make a word list first. Camelot's may suit you better with the words and numbers arranged more like your actual puzzles rather than all in a row like mine. Was just a fun one to try to solve.

Column A has your first words, typed in

B2 =XLOOKUP(MID($A2,COLUMN()−1,1),Table 1::$1:$1,Table 1::$2:$2,"",0)

Fill across to M then fill down with all

Column N is a math operator, typed in

Column O has your second words, typed in

P2 =XLOOKUP(MID($O2,COLUMN()−15,1),Table 1::$1:$1,Table 1::$2:$2,"",0)

Fill across to AA

AB=CONCAT(B2:M2)

AC=CONCAT(P2:AA2)

AD=SWITCH(N2,"+",AB2+AC2,"-",AB2−AC2,"*",AB2×AC2,"/",AB2÷AC2,"")

AE=IF(OR(LEN(AB2)≠LEN($A2),LEN(AC2)≠LEN(O2)),"ERROR","")

Fill down with formulas P2 through AE2

Hide column B:M and P:AA

Column AE will say "ERROR" if one of the words has letters that are not in the code word.

Also, in my first post (not this version), use CONCAT(B3:M3). I realized that words beginning in M will lose the leading 0 with the formula I gave earlier.

> I haven't tried this out yet but how does it work if I want to generate a list of words that can be made from, in this instance, MISTAKENLY, and assign the numerical values to each letter in the words, and then pick and choose which words I'll ultimately use for the arithmetic problems?

Are you asking for the spreadsheet to come up with a list of words? hat's a whole different problem. My model is based on you entering the words and it giving you a resultant 'score'. There's no mechanism to generate a list of words that fit the master word. I can't even think how you might do that in Numbers.

If you're OK to provide the words, the sheet can be modified in many ways. Speaking of which, I've come up with a revision that simplifies the addition, and run into a problem with the subtraction model... I'll post an update soon.

I think you will need to give a more complete example of what you are trying to do and why you are trying to do it (what problem you are trying to solve.)

SG