Normally I use Google Sheets, but I recently switched to a MacBook and want to use Numbers instead of Google Sheets.
I want to use a formula to see if a number is a palindrome. In Google Sheets I have 2 formulas that work, but they don’t work or don’t work well in Numbers.
In column A is the date and time as epoch timestamp. For example 17 September 2021 15:16:16 is 1631891776. The formula is in column B.
Formula 1:
IF(SUMPRODUCT(−−MID(SUBSTITUTE(A1;” “;””);ROW(INDIRECT(“1:”&LEN(SUBSTITUTE(A1;” “;””))));1)=MID(SUBSTITUTE(A1;” “;””);LEN(SUBSTITUTE(A1;” “;””))+1−ROW(INDIRECT(“1:”&LEN(SUBSTITUTE(A1;” “;””))));1))=LEN(SUBSTITUTE(A1;” “;””));”yes”;”no”)
This formula doesn’t give a result, but I get the following notification: The function “SUMPRODUCT” expects a vector but found “FALSE”.
If I take the formula apart I get the following answers:
SUMPRODUCT−−MID(SUBSTITUTE(A1;” “;””);ROW(INDIRECT(“1:”&LEN(SUBSTITUTE(A1;” “;””))));1)=MID(SUBSTITUTE(A1;” “;””);LEN(SUBSTITUTE(A1;” “;””))+1−ROW(INDIRECT(“1:”&LEN(SUBSTITUTE(A1;” “;””))));1) = here I get the notification
−−MID(SUBSTITUTE(A1;” “;””);ROW(INDIRECT(“1:”&LEN(SUBSTITUTE(A1;” “;””))));1)=MID(SUBSTITUTE(A1;” “;””);LEN(SUBSTITUTE(A1;” “;””))+1−ROW(INDIRECT(“1:”&LEN(SUBSTITUTE(A1;” “;””))));1) = FALSE
MID(SUBSTITUTE(A1;” “;””);ROW(INDIRECT(“1:”&LEN(SUBSTITUTE(A1;” “;””))));1) = 1
MID(SUBSTITUTE(A1;” “;””);LEN(SUBSTITUTE(A1;” “;””))+1−ROW(INDIRECT(“1:”&LEN(SUBSTITUTE(A1;” “;””))));1) = 6
LEN(SUBSTITUTE(A1;” “;””)) = 10
Formula 2:
IF(SUMPRODUCT((MID(A1;ROW(OFFSET($A$1;;;LEN(A1)));1)=MID(A1;LEN(A1)−ROW(OFFSET($A$1;;;LEN(A1)))+1;1))+0)=LEN(A1);”yes”;”no”)
With this formula I get “no” with every number and there is a blue triangle in the top left corner of the cell with the message: The formula uses a Boolean in place of a number.
If I take the formula apart I get the following answers:
SUMPRODUCT((MID(A1;ROW(OFFSET($A$1;;;LEN(A1)));1)=MID(A1;LEN(A1)−ROW(OFFSET($A$1;;;LEN(A1)))+1;1))+0) = 0 (here I get the blue triangle)
MID(A1;ROW(OFFSET($A$1;;;LEN(A1)));1) = 1
MID(A1;LEN(A1)−ROW(OFFSET($A$1;;;LEN(A1)))+1;1) = 6
LEN(A1) = 10
When I change the number 1631891776 to 1631881361, then the answers 6 in both formulas changes to 1, and in formula 2 the answer 0 changes to 1. But the answer from the IF formula doesn’t change.
How can I fix this?
Thanks in advance.
We want to know if the epoch timestamp is a palindrome, prime or palinprime number. These are numbers that don’t come up often. We are in a rabbit hole of decoding the epoch timestamp for a project. We think that a combination of these numbers is a key to unlocking the map.
—–
Taya
This challenge interested me so I created something from scratch in Numbers. I didn't try to use any of your existing formulas since they a probably using some special Google Sheets functionality. So I just created if from scratch.
In column A I have the numbers. In columns C through W I have some simple functions to determine if that digit matches the digit at the opposite end. So C2 holds the following:
MID($A2,COLUMN()−2,1)=MID($A2,LEN($A2)−COLUMN()+3,1)
It is simply comparing the first digit to the last digit. COLUMN()-2 matches 1 since COLUMN() for C is 3. Then LEN($A2)−COLUMN()+3 matches the last digit, like 7 if it is a 7-digit number.
Then you put that same formula into C2 through W2. You get a bunch of TRUE and FALSE values, and then errors after the length of the number. The errors don't matter.
Then in B2 you use the AND function to see if all of the values are TRUE. It uses INDIRECT to build something like AND(INDIRECT("R2C3:R2C9")). The formula is:
AND(INDIRECT("R"&ROW()&"C3:R"&ROW()&"C"&(2+LEN($A2)),FALSE))
So then you get TRUE in B2 if the number is a palindrome. Copy and paste down the rows and you can test a list of numbers in column A. I could probably just test the first half of the digits as there is no need to test the second half if the first half matches, right?
Here's a download: Palindrome.numbers.zip