• We are pleased to announce that the winner of our Feedback Prize Draw for the Winter 2024-25 session and winning £150 of gift vouchers is Zhao Liang Tay. Congratulations to Zhao Liang. If you fancy winning £150 worth of gift vouchers (from a major UK store) for the Summer 2025 exam sitting for just a few minutes of your time throughout the session, please see our website at https://www.acted.co.uk/further-info.html?pat=feedback#feedback-prize for more information on how you can make sure your name is included in the draw at the end of the session.
  • Please be advised that the SP1, SP5 and SP7 X1 deadline is the 14th July and not the 17th June as first stated. Please accept out apologies for any confusion caused.

Excel Challenge #2

S

Steve Hales

ActEd Tutor
Staff member
You've got Excel skills? Let's see.

It worked out really well last month, so here's the second in the series of Excel challenges. The rules are simple:
  • the submission must solve the stated problem
  • the solution must be limited to a single formula (which clearly not CA2-compliant!)
  • the shortest formula wins.
Problem #2

Construct a formula to check whether the contents of cell A1 is a palindrome or not; the output from the formula should be either "Palindrome" or "No palindrome". The formula should be case-insensitive, but it must respect punctuation and spaces. The following examples can be used to test your method.

="eye" --> "Palindrome"
="eyes" --> "No palindrome"
="aviva" --> "Palindrome"
="Aviva" --> "Palindrome"
="aibohphobia" --> "Palindrome"
="13631" --> "Palindrome"
="136310" --> "No palindrome"
="Never odd or even" --> "No palindrome"
="step on no pets" --> "Palindrome"
="step on no pet's" --> "No palindrome"
 
Last edited:
My initial formula is:

=IF(A1="eye","Palindrome",IF(A1="eyes","No palindrome",IF(A1="aviva","Palindrome",IF(A1="Aviva","Palindrome",IF(A1="aibohphobia","Palindrome",IF(A1=13631,"Palindrome",IF(A1=136310,"No palindrome",IF(A1="Never odd or even","No palindrome",IF(A1="step on no pets","No palindrome",IF(A1="step on no pet's","No palindrome",""))))))))))

It looks way too long though :eek:
 
Imidinho said:
My initial formula is:

=IF(A1="eye","Palindrome",IF(A1="eyes","No palindrome",IF(A1="aviva","Palindrome",IF(A1="Aviva","Palindrome",IF(A1="aibohphobia","Palindrome",IF(A1=13631,"Palindrome",IF(A1=136310,"No palindrome",IF(A1="Never odd or even","No palindrome",IF(A1="step on no pets","No palindrome",IF(A1="step on no pet's","No palindrome",""))))))))))

It looks way too long though :eek:
Click to expand...

Hi Imidinho,
even your formula works well for given examples....... but what if the cell A1 has (lol or non or xxy etc) ?
 
Sneaking in with another MATLAB Answer:
a = 'Enter String';
if sum(a == a(end:-1:1))==length(a); disp('Palindrome'); else disp('No Palindrome'); end;

------------
>> a = 'aviva';
>> if sum(a == a(end:-1:1))==length(a); disp('Palindrome'); else disp('No Palindrome'); end;

Palindrome

-----------------
>> a = 'Aviva';
>> if sum(a == a(end:-1:1))==length(a); disp('Palindrome'); else disp('No Palindrome'); end;

No Palindrome
 
Last edited:
Code: 
=IF(AND(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=MID(A1,LEN(A1)+1-ROW(INDIRECT("1:"&LEN(A1))),1)),"Palindrome", "No palindrome")
 
Another variation. neet to shorten

Hemant Rupani said:
Code: 
=IF(AND(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=MID(A1,LEN(A1)+1-ROW(INDIRECT("1:"&LEN(A1))),1)),"Palindrome", "No palindrome")
Click to expand...

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), "Palindrome", "No Palindrome!")
 
Check this

=IF(MID(A1,LEN(A1)-ROW(OFFSET(A1,,,LEN(A1)))+1,1)=MID(A1,ROW(OFFSET(A1,,,LEN(A1))),1),"Palindrome","No palindrome")
 
Last edited by a moderator:
satyam said:
=IF(MID(A1,LEN(A1)-ROW(OFFSET(A1,,,LEN(A1)))+1,1)=MID(A1,ROW(OFFSET(A1,,,LEN(A1))),1),"Palindrome","No palindrome")
Click to expand...

Hi Satyam,
here IF Function test only first single logical value..... it just check first & last character.
for example:- "A1:step on no pet's" gives Palindrome(it just check first & last s not middles)..... But it is No Palindrome.
 
Oxymoron said:
Sneaking in with another MATLAB Answer:
Click to expand...

I love the complete disregard (again!) for the fact that this is an EXCEL challenge. Other languages are going to be more efficient; for example if we were using VBA then the StrReverse function would be perfect. For example
Code: 
IF StrReverse(a) = a Then ...
But we're not :)
 
Steve Hales said:
I love the complete disregard (again!) for the fact that this is an EXCEL challenge. Other languages are going to be more efficient; for example if we were using VBA then the StrReverse function would be perfect. For example
Code: 
IF StrReverse(a) = a Then ...
But we're not :)
Click to expand...

I'm not competing with others Steve - just trying to troll this thread!
 
Hemant seems to be taking this one again.
=IF(AND(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=MID(A1,LEN(A1)+1-ROW(INDIRECT("1:"&LEN(A1))),1)),"Palindrome", "No palindrome")
Click to expand...
with minor refinements is 117 characters.

Assuming Steve has a solution, perhaps in future he could give us his character count in advance.
 
Last edited by a moderator:
didster said:
Assuming Steve has a solution, perhaps in future he could give us his character count in advance.
Click to expand...
My original character count was 125. However, inspired by Hemant's use of the AND function (and a sneaky trick with "alindrome"), this one comes in at 102;
Code: 
IF(AND(IF(ROW(A:A)>LEN(A1),1,MID(A1,ROW(A:A),1)=MID(A1,LEN(A1)+1-ROW(A:A),1))),"P","No p")&"alindrome"

There must be another way of tackling the problem. Rather than comparing pairs of characters, is there another way of analysing a string for this property?
 
The "alindrome" trick is the "minor refinement" I had in mind.
Looking for a spot of genius like use of Match in the last challenge.
 
So @didster I get the clue by the help of Steve's sneaky trick with "alindrome";)
Code: 
IF(AND(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=MID(A1,LEN(A1)+1-ROW(INDIRECT("1:"&LEN(A1))),1)),"P","No p")&"alindrome"
 
New approach

Here's another way of testing for palindromes without explicitly comparing pairs of characters. If the contents of cell A1 is palindromic then this quantity is zero;

Code: 
=SUM(IFERROR(CODE(MID(UPPER(A1),ROW(A:A),1))*COS((ROW(A:A)-1)*PI()/(LEN(A1)-1)),0))

Don't think the converse is true though - so some false positives might arise. Pretty sure that improving the implementation of the CODE function could remove this doubt (maybe something like CODE^2 etc).

Bonus marks for using PI()? ;)
 
Great!!!:D now I can get rid of INDIRECT with the help of IFERROR.
Thanks Steve!:)

Code: 
=IF(SUM(--IFERROR(MID(A1,ROW(A:A),1)=MID(A1,LEN(A1)+1-ROW(A:A),1),0))=LEN(A1),"P","No p")&"alindrome"
count down to 100

and if you don't mind No palindrome to be No Palindrome, then
Code: 
=IF(SUM(--IFERROR(MID(A1,ROW(A:A),1)=MID(A1,LEN(A1)+1-ROW(A:A),1),0))=LEN(A1),,"No ")&"Palindrome"
Count would be 97.
 
I can get it down to 93 by replacing the use of the MID() function with LEFT(RIGHT(),1) to extract the letters from the end of the word moving in:

Code: 
=IF(SUM(IF(MID(A1,ROW(A:A),1)=LEFT(RIGHT(A1,ROW(A:A)),1),1,0))=LEN(E6),"","No ")&"Palindrome"

(I was cheeky and I took Hermant's wonderful idea of sticking the word "No" before the word "Palindrome" to reduce the character count!).
 
maz1987 said:
(I was cheeky and I took Hermant's wonderful idea of sticking the word "No" before the word "Palindrome" to reduce the character count!).
Click to expand...

actually It was Steve's;)

and yes! you can also get rid of ""
Code: 
=IF(SUM(IF(MID(A1,ROW(A:A),1)=LEFT(RIGHT(A1,ROW(A:A)),1),1,0))=LEN(E6),,"No ")&"Palindrome"
now your count down to 90
 
Team work!

edit: actually it's 91, as I did not have a " " in the original formula, but rather a "". So it only reduces it by 2.
 
You must log in or register to reply here.
Back
Top