***WINNERS ANNOUNCEMENT*** June/July 2008 Challenge of the Month

[wsjackman]

a (to me) more traditional equation would be
=INDIRECT("E"&SUMPRODUCT(NOT(ISERR(FIND($D$2:$D$10,A2)))*ROW($D$2:$D$10)))

an alternate would be to name the "color" range and when new colors were added, just change the named range

=INDIRECT("E"&SUMPRODUCT(NOT(ISERR(FIND(color,A2)))*ROW(color)))

with color defined as d2:d10

[Daniel Ferry]

My idea was this for B2 and down (CSE, of course):

=OFFSET(D$1,SUM((IFERROR(FIND(D$1:D$10,A2),0)>0)*ROW(D$1:D$10))-1,1)

While that may not get me the win again this year since Harry Houdini's is a slicker approach, his can be improved upon. This is shorter by a few characters and works well:

=LOOKUP(2^9,FIND(D$2:D$10,A2),E$2:E$10)

Great job Harry.

[Daniel Ferry]

I meant this for Harry's formula:

=LOOKUP(9^6,FIND(D$2:D$10,A2),E$2:E$10)

which will work for all of the rows in 2007, and earlier versions. From a character count perspective, I do not think it's possible to improve this and still maintain the ability to copy the formula down.

[Daniel Ferry]

By the way, 9^5 is sufficient (in Harry's approach) since the limit to the number of characters in a cell is now 32,000 in Excel 2007. The limit was just 1,000 characters in prior versions. These alarmingly large numbers are overkill: the "color" could at most be 32,000 characters into the string in a cell, so 9^4 is too small to account for the "ultimate" case, while 9^5 is overkill but will work in any situation, without exception.

[RichardSchollar]

Originally Posted by Daniel Ferry

By the way, 9^5 is sufficient (in Harry's approach) since the limit to the number of characters in a cell is now 32,000 in Excel 2007. The limit was just 1,000 characters in prior versions. These alarmingly large numbers are overkill: the "color" could at most be 32,000 characters into the string in a cell, so 9^4 is too small to account for the "ultimate" case, while 9^5 is overkill but will work in any situation, without exception.

The maximum length of a cell has been 32767 since at least xl2000 (it was 32000 in xl97 per xl97's specification limits). 2^15 carries meaning as it is exactly 1 more than the maximum length of a cell in xl2000 and above.

[wsjackman]

9^5 is greater than 2^15 with fewer characters.


Is there any penalty, Time, processing, etc., for using a number greater than is needed?
similarly, is there any penalty, for using a number raised to a power vs using the written out number?
also, is there any penalty, for using a named constant rather than having the value in the formula

I have been bit by Excel's floating decimal method before.

[Daniel Ferry]

Richard:

I was referring to the number of characters that can be printed from a cell. The new limit is 32k; the old is 1k. This comes from the Excel Team Blog at MSDN:

http://blogs.msdn.com/excel/archive/...26/474258.aspx

At any rate, 9^5 works in all cases and is the most compact method possible. It is also slightly faster than 2^15 (less multiplications going on). But that takes us in a different direction. I have been arguing for compactness of formula. The QUICKEST formula would replace all of these large numbers generators with the constant:

32767

[wsjackman]

Originally Posted by Daniel Ferry

I meant this for Harry's formula:

=LOOKUP(9^6,FIND(D$2:D$10,A2),E$2:E$10)

which will work for all of the rows in 2007, and earlier versions. From a character count perspective, I do not think it's possible to improve this and still maintain the ability to copy the formula down.

Using named ranges [c =$D$2:$D$10 and n =$E$2:$E$10 x =9^6]
charactercount is reduced by

=LOOKUP(x,FIND(c,A2),n)

[wsjackman]

typical typos for me
c needs to be cc, n needs to be nn, x needs to be xx

[Daniel Ferry]

Originally Posted by wsjackman

Using named ranges [c =$D$2:$D$10 and n =$E$2:$E$10 x =9^6]
charactercount is reduced by

=LOOKUP(x,FIND(c,A2),n)

Obviously my post meant that my improvement on Houdini's formula was the shortest character count possible WITHOUT resorting to named formulas. One could simpy name any of the formulas that work, "zz".

Now all you need to enter is:

=zz

But that's pointless.

My improvement still reigns as the SHORTEST character count possible WITHOUT named formulas.

HOWEVER, dynamic ranges would be an improvement usability-wise, for the lookup tables...