Excel Challenge #5

[Steve Hales]

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%,"")))))))))

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]

I've had the following submission via email:

=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?

 

[tiger]

I've had the following submission via email:

=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?

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:

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

 

[satyam]

I've had the following submission via email:

=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?

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.

 

[Bodhisattva]

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:

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

Slight shortening by judicious choice of denominator:

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.

 

[Steve Hales]

judicious choice of denominator

What an excellent idea

That opens up this possibility:

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

70 characters

 

[Bodhisattva]

What an excellent idea

That opens up this possibility:

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

70 characters

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. (But mainly I wish I'd thought of it!)

Still, at least it isn't MATLAB

Can you award the prize to yourself?

 

[Oxymoron]

Still, at least it isn't MATLAB

I protest against this post on the grounds that it's becoming a running joke!

 

[Steve Hales]

a bit, well, inelegant.

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

 

[didster]

Nested IF statements are a nightmare to unpick. ....
... how can this logical nightmare be simplified within a single formula?

is not really the point, is it?

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]

not really the point, is it?

No, not really. Maybe "compressed" would have been more accurate than "simplified"

 

[Bodhisattva]

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

Sincere apologies Steve, no offence intended!

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.