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 #5

Discussion in 'Off-topic' started by Steve Hales, Jun 12, 2015.

  1. Steve Hales

    Steve Hales ActEd Tutor Staff Member

    You've got Excel skills? Let's see.

    Last month's challenge was really tough (I guess that's why it's called a "challenge"), so this month is designed to be a little more inclusive.

    Here are the usual rules.
    • Create a single Excel formula to solve the stated problem.
    • The shortest formula wins (when calculating the length of the formula there's no need to include the leading "=", or the curly braces that Excel uses to represent array formulae).
    • The formula's number format can be set to whatever you want.
    • Conditional formatting is not allowed.
    Problem #5

    Nested IF statements are a nightmare to unpick. I found the following post on an Excel help forum;

    Surely we can help them out? Using a VLOOKUP would probably be the most natural solution, but how can this logical nightmare be simplified within a single formula?

    Good luck!
     
    Steve Hales, Jun 12, 2015
    #1
  2. Steve Hales

    Steve Hales ActEd Tutor Staff Member

    I've had the following submission via email:
    Code:
    =CHOOSE(SUBSTITUTE(G86,"Text",0),1.75%,1.75%,1.75%,0.01%,0.5%,0,2.15%,2.15%,3.3%,3.3%,3.3%,3.3%,2.4%,2.4%,1.5%,1.5%,1.5%,1.5%,1.25%,1.25% )
    It does the job, but it's pretty long at 138 characters.
    Improvements?
     
    Steve Hales, Jun 15, 2015
    #2
  3. tiger

    tiger Ton up Member

    Same logic but some optimisations:
    - use MID instead of SUBSTITUTE. (Function doesn't seem to mind if no. of characters < start_num + num_chars)
    - remove % and add /100 at the end
    - *10 (gets rid of 12 decimal points) and change end to /1000

    gives:
    Code:
    CHOOSE(MID(g86,5,2),17.5,17.5,17.5,.1,5,0,21.5,21.5,33,33,33,33,24,24,15,15,15,15,12.5,12.5 )/1000
    96 chars?
    Will try to come up with something more creative
     
    Last edited: Jun 15, 2015
    tiger, Jun 15, 2015
    #3
  4. satyam

    satyam Member

    One small improvement could be not to use "%" with all the values and concatenate it will "&" in the end. Like:

    CHOOSE(SUBSTITUTE(G86,"Text",0),1.75,1.75,1.75,0.01,0.5,0,2.15,2.15,3.3,3.3,3.3,3.3,2.4,2.4,1.5,1.5,1.5,1.5,1.25,1.25)&"%"

    This brings down the formula to 122 characters.
     
    satyam, Jun 15, 2015
    #4
  5. Bodhisattva

    Bodhisattva Member

    Slight shortening by judicious choice of denominator:

    Code:
    CHOOSE(MID(g86,5,2),35,35,35,0.2,10,0,43,43,66,66,66,66,48,48,30,30,30,30,25,25)/2000
    Down to 85?

    I think the best we'll get is a variation on this theme.
     
    Last edited by a moderator: Jun 15, 2015
    Bodhisattva, Jun 15, 2015
    #5
  6. Steve Hales

    Steve Hales ActEd Tutor Staff Member

    What an excellent idea :)

    That opens up this possibility:

    Code:
    MID("x353535.210004343666666664848303030302525",MID(G86,5,2)*2,2)/2000
    70 characters
     
    Steve Hales, Jun 15, 2015
    #6
  7. Bodhisattva

    Bodhisattva Member

    Half of me thinks that that's very cunning, Steve. The other half of me thinks that using a big text string to look up numbers is a bit, well, inelegant. :p (But mainly I wish I'd thought of it!)

    Still, at least it isn't MATLAB :D

    Can you award the prize to yourself?
     
    Last edited by a moderator: Jun 15, 2015
    Bodhisattva, Jun 15, 2015
    #7
  8. Oxymoron

    Oxymoron Ton up Member

    I protest against this post on the grounds that it's becoming a running joke! :p
     
    Oxymoron, Jun 16, 2015
    #8
  9. Steve Hales

    Steve Hales ActEd Tutor Staff Member

    INELEGANT!? It certainly isn't pretty. I think it's also quite a long way from what the original forum poster was after, but at least it's short :D
     
    Steve Hales, Jun 16, 2015
    #9
  10. didster

    didster Member

    is not really the point, is it?

    Code:
    MID("x353535.210004343666666664848303030302525",MID(G86,5,2)*2,2)/2000
    Real challenge is taking something like this and figuring out what it does. Challenge #6 perhaps?

    PS. Seems you've stumped the MATLAB guys this time.
     
    didster, Jun 16, 2015
    #10
  11. Steve Hales

    Steve Hales ActEd Tutor Staff Member

    No, not really. Maybe "compressed" would have been more accurate than "simplified" :eek:
     
    Steve Hales, Jun 17, 2015
    #11
  12. Bodhisattva

    Bodhisattva Member

    Sincere apologies Steve, no offence intended! :eek:

    It was definitely cunning to get the string to give exactly two digits to read off in all cases. I doff my hat to you, sir.
     
    Bodhisattva, Jun 17, 2015
    #12

Share This Page