Excel Challenge #5

S

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;

I have 9 levels of IF statements listed below. Not sure how I could consolidate it. Is there anything that can be done here?

=IF(OR(G86="Text1",G86="Text2",G86="Text3"),1.75%,
IF(G86="Text4",0.01%,
IF(G86="Text5",0.50%,
IF(G86="Text6",0.00%,
IF(OR(G86="Text7",G86="Text8"),2.15%,
IF(OR(G86="Text9",G86="Text10",G86="Text11",
G86="Text12"),3.30%,
IF(OR(G86="Text13",G86="Text14"),2.40%,
IF(OR(G86="Text15",G86="Text16",G86="Text17",
G86="Text18"),1.50%,
IF(OR(G86="Text19",G86="Text20"),1.25%,"")))))))))
Click to expand...
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!
 
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 said:
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?
Click to expand...

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 by a moderator:
Steve Hales said:
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?
Click to expand...

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.
 
tiger said:
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
Click to expand...

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:
Steve Hales said:
What an excellent idea :)

That opens up this possibility:

Code: 
MID("x353535.210004343666666664848303030302525",MID(G86,5,2)*2,2)/2000

70 characters
Click to expand...

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:
Steve Hales said:
Nested IF statements are a nightmare to unpick. ....
... how can this logical nightmare be simplified within a single formula?
Click to expand...
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.
 
Steve Hales said:
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
Click to expand...

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.
 
You must log in or register to reply here.
Back
Top