1. Posts in the subject areas are now being moderated. Please do not post any details about your exam for at least 3 working days. You may not see your post appear for a day or two. See the 'Forum help' thread entitled 'Using forums during exam period' for further information. Wishing you the best of luck with your exams.
    Dismiss Notice

Excel Challenge #2

Discussion in 'Off-topic' started by Steve Hales, Mar 13, 2015.

Page 1 of 2
  1. Steve Hales

    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: Mar 13, 2015
    Steve Hales, Mar 13, 2015
    #1
  2. Imidinho

    Imidinho Member

    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, Mar 13, 2015
    #2
  3. Hemant Rupani

    Hemant Rupani Senior Member

    Hi Imidinho,
    even your formula works well for given examples....... but what if the cell A1 has (lol or non or xxy etc) ?
     
    Hemant Rupani, Mar 13, 2015
    #3
  4. Oxymoron

    Oxymoron Ton up Member

    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: Mar 13, 2015
    Oxymoron, Mar 13, 2015
    #4
  5. Hemant Rupani

    Hemant Rupani Senior Member

    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")
     
    Hemant Rupani, Mar 14, 2015
    #5
  6. satyam

    satyam Member

    Another variation. neet to shorten

    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!")
     
    satyam, Mar 14, 2015
    #6
  7. satyam

    satyam Member

    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: Mar 14, 2015
    satyam, Mar 14, 2015
    #7
  8. Hemant Rupani

    Hemant Rupani Senior Member

    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.
     
    Hemant Rupani, Mar 14, 2015
    #8
  9. Steve Hales

    Steve Hales ActEd Tutor Staff Member

    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, Mar 14, 2015
    #9
  10. Oxymoron

    Oxymoron Ton up Member

    I'm not competing with others Steve - just trying to troll this thread!
     
    Oxymoron, Mar 14, 2015
    #10
  11. didster

    didster Member

    Hemant seems to be taking this one again.
    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: Mar 16, 2015
    didster, Mar 16, 2015
    #11
  12. Steve Hales

    Steve Hales ActEd Tutor Staff Member

    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?
     
    Steve Hales, Mar 16, 2015
    #12
  13. didster

    didster Member

    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.
     
    didster, Mar 17, 2015
    #13
  14. Hemant Rupani

    Hemant Rupani Senior Member

    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"
     
    Hemant Rupani, Mar 17, 2015
    #14
  15. Steve Hales

    Steve Hales ActEd Tutor Staff Member

    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()? ;)
     
    Steve Hales, Mar 21, 2015
    #15
  16. Hemant Rupani

    Hemant Rupani Senior Member

    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.
     
    Hemant Rupani, Mar 21, 2015
    #16
  17. maz1987

    maz1987 Member

    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, Mar 24, 2015
    #17
  18. Hemant Rupani

    Hemant Rupani Senior Member

    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
     
    Hemant Rupani, Mar 24, 2015
    #18
  19. maz1987

    maz1987 Member

    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.
     
    maz1987, Mar 24, 2015
    #19
  20. Hemant Rupani

    Hemant Rupani Senior Member

    :D hahaha yes
     
    Hemant Rupani, Mar 24, 2015
    #20
  21. Hemant Rupani

    Hemant Rupani Senior Member

    first = and {} won't be in count (as per the 1st challenge)
     
    Last edited: Mar 24, 2015
    Hemant Rupani, Mar 24, 2015
    #21
Page 1 of 2

Share This Page